asked on

MERGE ROWS IN SQL TO SHOW SUMMARIZED VIEW - STICHED QUERY ??

Hello,

I would like to merge the following representative data:

ID      Col2      Col3
1      A
1      A      A
1            A
1            C
1      B      F
1      B
2      T
2      T      T
2      T      V
2
2

So it appears as follows:
ID      Col2      Col3
1      A      A
1      B      F
1            C
2      T      T
2      T      V

the values don't need to line up as there is no relationship between Col2 and Col3. Just need to show a compressed / merged view of the possibilities of Col2 and Col3 for every ID.

Thanks a mil!

imran_fast

select A.id, A.col2, A.col3
from yourtable A
where
(A.col2 is not null and A.col3 is not null) or
A.col2 in (select B.Col2 from yourtable b where B.Id = A.ID group by B.Col2 having count(*) =1 )
or
A.col3 in (select c.Col2 from yourtable c where c.Id = A.ID group by c.Col2 having count(*) =1 )

ASKER CERTIFIED SOLUTION

imran_fast

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

salesprod

ASKER

Thanks! - don't know how you did it - but will try to figure it out. Gets me close enough to what i need...

ispaleny

--drop table yourtable
create table yourtable (
[ID] INTEGER NOT NULL
,Col2 CHAR(1) NULL
,Col3 CHAR(1) NULL
)
create clustered index i on yourtable ([ID])
insert      yourtable      values      (      1,      'A',      NULL      )
insert      yourtable      values      (      1,      'A',      'A'      )
insert      yourtable      values      (      1,      NULL,      'A'      )
insert      yourtable      values      (      1,      NULL,      'C'      )
insert      yourtable      values      (      1,      'B',      'F'      )
insert      yourtable      values      (      1,      'B',      NULL      )
insert      yourtable      values      (      2,      'T',      NULL      )
insert      yourtable      values      (      2,      'T',      'T'      )
insert      yourtable      values      (      2,      'T',      'V'      )
insert      yourtable      values      (      2,      NULL,      NULL      )
insert      yourtable      values      (      2,      NULL,      NULL      )

SELECT DISTINCT
       A.[ID]
      ,COALESCE(A.Col2, C.Col2) as Col2
      ,COALESCE(A.Col3, B.Col3) as Col3
FROM
      yourtable as A
LEFT JOIN
      yourtable as B
ON
      A.Col3 is null and B.Col3 is not null and A.Col2 = B.Col2
LEFT JOIN
      yourtable as C
ON
      A.Col2 is null and C.Col2 is not null and A.Col3 = C.Col3
WHERE
      A.Col2 IS NOT NULL
      OR
      A.Col3 IS NOT NULL