Home > JOINS, SQL Tips > Convert multiple Rows into a single column

Convert multiple Rows into a single column

Just replied an answer on a SQL forum (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148860), so thought to post this as well for future reference.

How will you output the following record set:
123456788  City     399.99
123456788  County   499.99
123456788  Flood    299.99
123456788  Hazzard  199.99
123456789  City     333.99
123456789  County   444.99
123456789  Flood    222.99
123456789  Hazzard  111.99

... into following pattern:

id            Hazzard     Flood      City       County
123456788     199.99      299.99     399.99     499.99
123456789     111.99      222.99     333.99     444.99

The following query will do it and convert the rows into columns:

select 123456788 as id,  'Hazzard' as des, 199.99 as val
into #tempTable
select 123456788, 'Flood', 299.99 union
select 123456788, 'City', 399.99 union
select 123456788, 'County', 499.99 union
select 123456789, 'Hazzard', 111.99 union
select 123456789, 'Flood', 222.99 union
select 123456789, 'City', 333.99 union
select 123456789, 'County', 444.99
select * from #tempTable

select a.id as id, a.val as Hazzard, b.val as Flood, c.val as City, d.val as County
from #tempTable a,#tempTable b,#tempTable c, #tempTable d
where a.id=b.id and b.id=c.id and c.id=d.id
and a.des='Hazzard' and b.des='Flood' and c.des='City' and d.des='County'

drop table #tempTable

The above select code is with a simple old style where clause with multiple tables joining. The code could be also converted to self-joins.

  1. October 7, 2010 at 10:21 am

    Rows into Columns, MSDN forum post link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/66c43e11-bd6e-4bd8-88f5-cdc33beaf7c0Code:create table T (SID int, dim_name varchar(100), dim_value varchar(100))insert into Tselect 1, 'weight', '44' unionselect 1, 'color', 'silver' unionselect 1, 'price', '7.19' unionselect 2, 'weight', '450' unionselect 3, 'weight', '100' unionselect 2, 'color', 'brown' unionselect 2, 'price', '37'–basic (will work with 2000, for 2005 see query 1 & 2)select distinct SID, (select dim_value from T where dim_name='weight' and SID=T1.SID) as 'weight', (select dim_value from T where dim_name='color' and SID=T1.SID) as 'color', (select dim_value from T where dim_name='price' and SID=T1.SID) as 'price'from T T1–query1select * from T pivot (max(dim_value) for dim_name in ([weight],[color],[price]))pvt—Query 2declare @dim_names nvarchar(max)set @dim_names = STUFF((select distinct ',[' + dim_Name + ']' from T for XML path('')),1,1,'')declare @sql nvarchar(max)set @sql = 'select * from T pivot (max(dim_value) for dim_name in (' + @dim_names + '))pvt'exec(@Sql)drop table T

  2. December 24, 2011 at 7:48 am

    I was just going through your blog pretty informative…. you could also perform it in this manner:-

    select id,[Hazzard],[Flood],[City],[County] from
    (select id,des,val from #tempTable) p
    pivot(sum(val) For des in ([Hazzard],[Flood],[City],[County]) ) AS PVT
    order by id

  1. No trackbacks yet.

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 )

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: