Should I use pivot for this - 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

Should I use pivot for this

Author

Topic

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2014-06-09 : 11:18:10

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-09 : 13:55:25

This takes care of the sample data:


with data(club, Name) as (select * from (values
	(1, 'Bill'),
	(1, 'Tony'),
	(2, 'James'),
	(1, 'Alan'),
	(3, 'John'),
	(3, 'Ian')
) d(a,b) ),

src as (
select club, Name, 'name' + convert(varchar(5), row_number() over(partition by club order by club)) as namen
from data
)

select club, name1, name2, name3 from src
pivot (max(Name) for namen in (name1, name2, name3)) pvt

but if you want unlimited club members, you'll have to make it dynamic. Basically the idea is to build up a list of column names for the final select and the value names in the pivot then execute a dynamic sql statement using those.

Here's one way to do that:


create table #data (club int, Name varchar(50))
insert into #data values
	(1, 'Bill'),
	(1, 'Tony'),
	(2, 'James'),
	(1, 'Alan'),
	(3, 'John'),
	(3, 'Ian')

declare @sql nvarchar(max) = '
select club, {collist} 
from (
	select club, Name, ''name'' + convert(varchar(5), row_number() over(partition by club order by club)) as names
from #data
)src
pivot (max(Name) for names in ({collist})) pvt
'
declare @collist nvarchar(max)

;with colnames(colname) as (
	select distinct 'name' + convert(varchar(5), row_number() over(partition by club order by club))
	from #data
)

select @collist = (
	select ', ' + colname
	from colnames
	for xml path('')
	)

set @sql = replace(@sql, '{collist}', stuff(@collist,1,1,''))
exec (@sql)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources