SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Should I use pivot for this
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

godspeedba
Yak Posting Veteran

90 Posts

Posted - 06/09/2014 :  11:18:10  Show Profile  Reply with Quote
I have a table like this
club | Name
1 | Bill
1 | Tony
2 | James
1 | Alan
3 | John
3 | Ian

I need to transform this data to look like this.

club | name1 | name2 | name3
1 | Bill | Tony | Alan
2 | James
3 | John | Ian

I've looked at pivot tables and how they work but cannot understand how to do this. Can anyone help?

gbritton
Aged Yak Warrior

857 Posts

Posted - 06/09/2014 :  13:55:25  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000