I have a table Test with two columns.
Id Value1 Value2 value3
1 A B C
This is a pivot, but you don't have a column for the pivoting.
row_number() can provide that. I usually use conditional aggregations for this.
select id, max(case when seqnum = 1 then value end) as value1, max(case when seqnum = 2 then value end) as value2, max(case when seqnum = 3 then value end) as value3 from (select t.*, row_number() over (partition by id order by (select null)) as seqnum from t ) t group by id;
Note that SQL tables represent unordered sets. So, there is no information about ordering and the values could be in any order. If a column does specify the ordering, then include that in the
order by rather than