Dec 11, 2012

Merging of Rows


I got a question two days back on my facebook account "Rahul Vairagi" in my DW group "SQL Server & Data warehousing Experts". The question was like this:
-------------------------------------------------------hi friend i have a small doubt in sql plz tell me 
how to implement the query

table data contain like

...

UK, id , name , sal , deptno
1, 1 , a , null , null
1, null, null , 100 , 10
2, 2 , b , null , null
2, null , null , 200 , 20
3, 3 , c , null , null
3, null , null , 300 , 30

but i want output like

id, name ,sal ,deptno
1 , a , 100 ,10
2, b ,200 ,20
3 , c ,300 ,30

and i am trying but not getting desired output .plz tell me where should be the query"

-------------------------------------------------------------------------


I tried a lot came-up with a solution


  • SELECT distinct t.[UK],
    (
    SELECT top 1 t1.Id
    FROM [sample] t1
    WHERE t1.UK = t.[UK] AND t1.UK IS NOT NULL

    ) ID,

    (
    SELECT top 1 t1.name
    FROM [sample] t1
    WHERE t1.UK = t.[UK] AND t1.name IS NOT NULL

    ) name,

    (
    SELECT top 1 t2.sal
    FROM [sample] t2
    WHERE t2.[UK] = t.[UK] AND t2.sal IS NOT NULL
    ) Salary,

    (
    SELECT top 1 t2.deptno
    FROM [sample] t2
    WHERE t2.[UK] = t.[UK] AND t2.deptno IS NOT NULL
    --LIMIT 1
    ) deptnum

    FROM [sample] t
    GROUP BY t.[UK]