I ran into a problem writing a query today, and took the time to find the solution, so I thought I should share this with anyone who has the same problem. This solution should only work for SQL Server version 2005 and above.
create table #t (
id int
);
insert #t (id) values (1),(2),(3),(4),(5);
select * from #t;
The above code should result as follows:
id -------- 1 2 3 4 5
But what if you want it as a single row with multiple columns?
id1 id2 id3 id4 id5 -------------------------- 1 2 3 4 5
This can be done by using the following solution:
with tst as( select id, row_number() over(order by id) as rn from #t) select max(case when rn=1 then id end) as id1, max(case when rn=2 then id end) as id2, max(case when rn=3 then id end) as id3, max(case when rn=4 then id end) as id4, max(case when rn=5 then id end) as id5 from tst
johnro 