CODEWITHSUNDEEP
sqlsql-serversql-server-2008
whastupduck
whastupduck

Reputation: 1166

pair up two rows in single row

I have this table:

A   B          C
1   Record 1   Type 1
2   Record 2   Type 2
3   Record 3   Type 1
4   Record 4   Type 2

I need to pair up rows by their values in C (Type 1 & Type 2) given that the first record with Type 1 must match with the nearest ID that with Type 2

Desired output:

A   B         C        A   B         C
1   Record 1  Type 1   2   Record 2  Type 2
3   Record 3  Type 1   4   Record 4  Type 2

I tried doing this in a query with 2 CTEs but I couldnt come up with the expected result:

WITH SET_A (A, B, C) AS

    (SELECT * FROM A WHERE C = 'Type 1'),

SET_B (A, B, C) AS
    (SELECT * FROM A WHERE C = 'Type 2')

SELECT * FROM SET_A CROSS JOIN SET_B;

Are there any other approach than using cross joins?

Upvotes: 0

Views: 3996

Answers (3)

PM 77-1
PM 77-1

Reputation: 13334

SELECT t1.a a1, t1.b b1, t1.c c1, t2.a a2, t2.b b2, t2.c c2
FROM Table1 t1 
JOIN Table1 t2 ON t1.c = 'Type 1' AND  t2.c = 'Type 2' AND t1.a < t2.a
WHERE t2.a  = (SELECT MIN(t3.a) FROM Table1 t3 WHERE t3.c = 'Type 2' AND t3.a > t1.a)

SQL Fiddle

Upvotes: 1

Malk
Malk

Reputation: 11983

Here you go. For each "Type 1" it will find the nearest subsequent (by id) "Type 2".

http://sqlfiddle.com/#!6/a5263/20

CREATE TABLE t 
(
  A int,
  B varchar(32),
  C varchar(32)
  );



insert into t values (1, 'Record 1', 'Type 1')
insert into t values (2, 'Record 2', 'Type 2')
insert into t values (3, 'Record 3', 'Type 1')
insert into t values (4, 'Record 4', 'Type 2')
insert into t values (5, 'Record 5', 'Type 1')
insert into t values (6, 'Record 6', 'Type 1a')
insert into t values (7, 'Record 7', 'Type 2')


;

with set_a as 
(
  select * from t where c = 'type 1'
)
, set_b as 
(
  select a, b, c, a_match = (select max(t2.a) from t t2 where t2.a < t.a and t2.c = 'type 1') 
  from t where c = 'type 2'
)
select set_a.* , a2 = set_b.a, b2 = set_b.b, c2 = set_b.c
from set_a
join set_b on set_b.a_match = set_a.a

Upvotes: 1

Manish Jangir
Manish Jangir

Reputation: 505

Try this one

SELECT * FROM yourtable t1, yourtable t2 WHERE t1.c=t2.c

Upvotes: -1

Related Questions