Transpose Values - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Transpose Values

Author

Topic

varunpant
Starting Member

3 Posts

Posted - 2008-11-05 : 15:57:50

Hi,
I want to transpose the values of a table using a query.

Orchestra

UserName	UserCode	Role
Archie		UC001		Composer
Archie		UC001		Singer
Betty		UC002		Composer
Betty		UC002		Singer
Veronica	UC003		Singer
Jughead	        UC004		Instrument
Betty		UC002		Instrument

I want to transpose the values like

UserName	UserCode	Composer    Singer 	Instrument
Archie		UC001		1		1	    0
Betty		UC002		1		1	    1
Veronica	UC003		0		1	    0
Jughead	        UC004		0		0	    1

Any ideas?
Thanks for your help,
Cheers,

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-11-05 : 16:14:38

read about PIVOT and UNPIVOT here

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-05 : 16:17:29

[code]
declare @sample table (
username varchar(20),
usercode varchar(10),
role varchar(20)
)

insert into @sample
select
'Archie','UC001','Composer'
union all
select 'Archie','UC001','Singer'
union all
select 'Betty','UC002','Composer'
union all
select 'Betty','UC002','Singer'
union all
select 'Veronica','UC003','Singer'
union all
select 'Jughead','UC004','Instrument'
union all
select 'Betty','UC002','Instrument'

select * from @sample

select distinct
a.username, b.* from
(select * from @sample
pivot
(
count(username)
for role in (Composer, Singer, Instrument)
) as p) b inner join @sample a on a.usercode = b.usercode
order by b.usercode
[/code]

varunpant
Starting Member

3 Posts

Posted - 2008-11-05 : 16:19:46

Thanks hanbingl and rohit,
Cheers,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 23:33:06

select usercode,username,
sum(case when role='composer' then 1 else 0 end) as composer,
sum(case when role='singer' then 1 else 0 end) as singer,
sum(case when role='Instrument' then 1 else 0 end) as Instrument
from @sample
group by usercode,username
order by usercode

varunpant
Starting Member

3 Posts

Posted - 2008-11-05 : 23:49:46

Thanks.
Yes, this is also another way,
Cheers,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources