How can I display repeating feilds horizontal using pivot

I have this data

ID      Name      Code      Type
1      Sam      205      office
2      Sam      778      home
3      Sam      678      cell
4      Bob      789      office
5      Bob      167      home
6      Tim      556      cell

I want to create a view that will display the data this way

Name      Code1      Type1      Code2      Type2      Code3      Type3
Sam        205         office         778         home          678         cell
Bob        789         office         167         home
Tim                                                                              556         cell


How can I do this using pivot in SQL Server 2005
vraj20Asked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
yep, need a cast there and missed a plus sign.
DECLARE @strSQL varchar(8000) 
DECLARE @COLS VARCHAR(8000) 


SELECT @COLS = 	coalesce(@COLS+',', '') + 
		'max(case when [rn] = ' + rn + ' then [Code] end) as [Code' + cast(rn as varchar) + ']' +
		', max(case when [rn] = ' + rn + ' then [Type] end) as [Type' + cast(rn as varchar) + ']' 

from (
	select distinct cast(row_number() over (partition by Name order by ID) as varchar) rn from yourtable
) a 

set @strSQL = 'select 	Name, ' + @cols + ' from (select *, row_number() over (partition by Name order by ID) rn from yourtable ) t1 group by Name'

exec(@strSQL)

Open in new window

0
 
ralmadaCommented:
try the below:

DECLARE @strSQL varchar(8000) 
DECLARE @COLS VARCHAR(8000) 


SELECT @COLS = 	coalesce(@COLS+',', '') + 
		'max(case when [rn] = ' + rn + ' then [Code] end) as [Code' + cast(rn as varchar) + ']' 
		', max(case when [rn] = ' + rn + ' then [Type] end) as [Type' + cast(rn as varchar) + ']' 

from (
	select distinct row_number() over (partition by Name order by ID) rn from yourtable
) a 

set @strSQL = 'select 	Name, ' + @cols + ' from (select *, row_number() over (partition by Name order by ID) rn from yourtable ) t1 group by Name'

exec(@strSQL)

Open in new window

0
 
vraj20Author Commented:
'max(case when [rn] = ' + rn + ' then [Code] end) as [Code' + cast(rn as varchar) + ']'

What does rn represent?
0
 
ralmadaCommented:
rn is just a column that I'm creating using the row_number() function to number code and types column like code1, code2, etc....
 
Hope this helps
0
 
vraj20Author Commented:
Thank you for your assistance. When I run your script I get these error message

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ', max(case when [rn] = '.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'a'.
0
All Courses

From novice to tech pro — start learning today.