Categories
Database Programming

SQL Server: Selecting multiple rows into a single row with multiple columns

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

Source

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s