Converting rows into columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting rows into columns

Author

Topic

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2009-02-18 : 14:04:24

Hi,

I have a table that has 4 columns (No, Name, Label and Val). Sample table like the following:

No Name Label Val
2333 Tom Salary 10000
2333 Tom Code A1
2333 Tom Grade C1
5666 Jim Salary 5000
5666 Jim Code A1
8999 Nick Grade D1

There are 3 distinct values in the label column(salary, code and grade). These needs to be converted to different columns.
I want the output like the following:

No Name Salary Code Grade
2333 Tom 10000 A1 C1
5666 Jim 5000 A1 NULL
8999 Nick NULL NULL D1

I went through the following weblog..
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx
But i could not modify that script that jeff put to accomodate to change it to column name..

Can anybody please give suggestions.

Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 14:32:28

[code]select
[No],
[Name],
Salary=case when Label ='Salary' then Val end,
Code= case when Label ='Code' then Val end,
Grade= case when Label ='Grade' then Val end
from
Yourtable[/code]

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-18 : 14:43:34

The example above will yield multiple rows per person. Try this instead:

select [no], [name],
salary = max(case when label = 'salary' then val end),
code = max(case when label = 'code' then val end),
grade = max(case when label = 'grade' then val end)
from [table]
group by [no], [name]

Or just for fun, you can start using SQL 2005 PIVOT syntax. It doesn't simplify your code much in this instance, but it's as good a time as any to start getting familiar with it. You may have a more complex situation in the future where PIVOT will be much more efficient than the max(case) alternative.

select [no], [name], p.salary, p.code, p.grade
from [table]
pivot (max(val) for label in (salary, code, grade)) as p

Hope this helps.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 15:04:40

quote:
Originally posted by SQLforGirls

The example above will yield multiple rows per person. Try this instead:

select [no], [name],
salary = max(case when label = 'salary' then val end),
code = max(case when label = 'code' then val end),
grade = max(case when label = 'grade' then val end)
from [table]
group by [no], [name]

Or just for fun, you can start using SQL 2005 PIVOT syntax. It doesn't simplify your code much in this instance, but it's as good a time as any to start getting familiar with it. You may have a more complex situation in the future where PIVOT will be much more efficient than the max(case) alternative.

select [no], [name], p.salary, p.code, p.grade
from [table]
pivot (max(val) for label in (salary, code, grade)) as p

Hope this helps.

He never asked for max of salary, max of code, max of code.. taking max doesn't make much sense either.

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2009-02-18 : 15:32:36

Hi Sakets2000, i tested the code that you provided. It did give multiple rows per person. I want to have only only row in the output, because there would be distinct values in the label column. I a sorry if i was not clear in my question. Thank you for your help !!..

I used the query that SQLForGirls gave. Since there are only distinct values the max function with the grouping worked fine in this case. Thanks !!!..

Thank to both of you !!

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-18 : 15:41:03

It only makes sense if he has one value of each type per person (which I assumed from his use of "3 distinct values in the label column" which I took to mean distinct values per person), and he wants output of 1 row per person (which I assumed from his sample output).

I suppose that may amount to too many assumptions. Good point, thanks.

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2009-02-18 : 16:30:07

Looks like the PIVOT is a cool feature.. I am using this in my query Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-18 : 17:43:47

Is your label going to be consistent? Otherwise you have to use Dynamic Pivot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 03:31:06

for dynamic pivot options refer

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

srihari nandamuri
Starting Member

6 Posts

Posted - 2009-02-19 : 08:59:07

Hi
It also works with Using Dynamic sql script .
I had a same issue but with multiple lables(not just 3 lables in ur case) in my main table.So i created below script which will add all the lables as columns dynamically and the values correspondingly.

declare @id int
declare @label varchar(5000)
declare @sql nvarchar(4000)

create table #temp (No int, Name varchar(500),Label varchar(500),Val varchar(500))
insert into #temp values(2333,'Tom', 'Salary', 10000)
insert into #temp values(2333 ,'Tom', 'Code', 'A1')
insert into #temp values(2333 ,'Tom', 'Grade', 'C1')
insert into #temp values(5666 ,'Jim', 'Salary', 5000)
insert into #temp values(5666,'Jim', 'Code', 'A1')
insert into #temp values(8999 ,'Nick', 'Grade', 'D1')

create table #final (No int, Name varchar(500))
create table #t1 (id int identity(1,1),label varchar(500))

insert into #final (no,name) select distinct no,name from #temp
insert into #t1 (label) select distinct Label from #temp

select @id =1
while (@id<=(select max(id) from #t1))
begin
select @label =label from #t1 where id=@id
select @sql ='alter table #final add '+@label +' varchar(500)'
exec sp_executesql @sql
select @id=@id +1
end

select @id =1
while (@id<=(select max(id) from #t1))
begin
select @label =label from #t1 where id=@id
select @sql = 'update a set ' + @label +' = val from #final a,#temp b where a.no=b.no and b.label='+''''+@label+''''
exec sp_executesql @sql
select @id=@id +1
end

select * from #final
drop table #final
drop table #t1
drop table #temp

Cheers
Srihari Nandamuri

srihari nandamuri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 09:18:12

you need to apply the dynamic script only if columns values to be pivoted are not static.

srihari nandamuri
Starting Member

6 Posts

Posted - 2009-02-19 : 09:23:30

Hi ,
The dynamic scrpt i used doesnt need any pivot.And in general case column values will not be static.

quote:
Originally posted by visakh16

you need to apply the dynamic script only if columns values to be pivoted are not static.

srihari nandamuri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 09:38:41

i know that. i was refering to PIVOT operator but was refering to pivoting of values as columns themselves.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources