Haminteu Haminteu -4 years ago 57
SQL Question

Edit 2 rows into 2 columns in a single result SQL

I have the following table:


WorkID WorkDesc
--------------------
1 ABCD
2 DEFG
3 HIJK


then I've the following table as the detail of table one:


WorkDetailID WorkID WorkDetailDesc
-----------------------------------------
1 1 001
2 1 002
3 2 006
4 2 007
5 3 015


Each WorkID is always have maximum 2 records and minimum is 1.

I want to have the following result:


WorkID WorkDesc WorkDetailID1 WorkDetailID2
-------------------------------------------------------
1 ABCD 1 2
2 DEFG 3 4
3 HIJK 5 null


Does anyone have an idea how to do that?

Thank you.

Answer Source

You can use pivot. I prefer conditional aggregation. In either case, you need a column for the pivoting. row_number() to the rescue:

select t1.workid, t1.workdesc, 
       max(case when t2.seqnum = 1 then t2.workdetailid end) as workdetailid1,
       max(case when t2.seqnum = 2 then t2.workdetailid end) as workdetailid2
from t1 join
     (select t2.*,
             row_number() over (partition by t2.workid order by t2.workdetailid) as seqnum
      from t2
     ) t2
     on t1.workid = t2.workid
group by t1.workid, t1.workdesc
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download