MS SQL Merge 2 rows to 2 columns.
I have a table with data like this:
idx Serial_No Model Line Lower Upper Spec. Data
34697 9KV4A11000003 T021-0 B 0.03 0.076 54 0.02
34698 9KV4A11000003 T021-0 B 0.03 0.076 54 0.01
43653 9KV4A11000034 TOK1-0 B 0.03 0.076 60 0.03
43654 9KV4A11000034 TOK1-0 B 0.03 0.076 60 0.03
..........................
Each Serial_No has 2 records (2 rows).
I want to select to data like this:
idx Serial_No Model Line Lower Upper Spec. Data Data2
34697 9KV4A11000003 T021-0 B 0.03 0.076 54 0.02 0.01
43653 9KV4A11000034 TOK1-0 B 0.03 0.076 60 0.03 0.03
..........................
(Moves the second row to new column Data2)
Please help me.
idx Serial_No Model Line Lower Upper Spec. Data
34697 9KV4A11000003 T021-0 B 0.03 0.076 54 0.02
34698 9KV4A11000003 T021-0 B 0.03 0.076 54 0.01
43653 9KV4A11000034 TOK1-0 B 0.03 0.076 60 0.03
43654 9KV4A11000034 TOK1-0 B 0.03 0.076 60 0.03
..........................
Each Serial_No has 2 records (2 rows).
I want to select to data like this:
idx Serial_No Model Line Lower Upper Spec. Data Data2
34697 9KV4A11000003 T021-0 B 0.03 0.076 54 0.02 0.01
43653 9KV4A11000034 TOK1-0 B 0.03 0.076 60 0.03 0.03
..........................
(Moves the second row to new column Data2)
Please help me.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks! I got it.
,row_number() over (partition by idx,serial_number,model,li
order by [data] desc) as rn
from yopurtable as x
)
select a.*
,[1] as [Data],[2] as [Data2]
from cte as a
pivot (max(data) for rn in ([1],[2])) as pvt
order by idx,serial_no,model,line,l