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:


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(
		row_number() over(order by id) as rn
	from #t)
	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


Leave a Reply

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

You are commenting using your 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