I am looking to return one row that merges multiple rows so for the example table:
ID | VAL1 | VAL2
1 | v1 | v2
1 | v3 |v4
I would like to return:
ID|VAL1|VAL2|VAL1_1|VAL2_1
1 | v1 | v2 | v3 | v4
Where ID|VAL1|VAL2|VAL1_1|VAL2_1 are the column headers
I dont believe a pivot can work as the values (v1 etc) can be any value.
Any help would be greatly appreciated
If you want a dynamic pivot, then you need dynamic SQL. But, you can do what you want with conditional aggregation:
select id,
max(case when seqnum = 1 then val1 end) as val1,
max(case when seqnum = 1 then val2 end) as val2,
max(case when seqnum = 2 then val1 end) as val1_1,
max(case when seqnum = 2 then val2 end) as val2_1
from (select t.*, row_number() over (partition by id order by id) as seqnum
from t
) t
group by id;
If your rows are ordered in a particular way, then you should modify the order by.
