Solved

How can I display repeating feilds horizontal using pivot

Posted on 2010-08-23
5
310 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:vraj20
  • 3
  • 2
5 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 33503865
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
 

Author Comment

by:vraj20
ID: 33505352
'max(case when [rn] = ' + rn + ' then [Code] end) as [Code' + cast(rn as varchar) + ']'

What does rn represent?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33505418
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
 

Author Comment

by:vraj20
ID: 33511530
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
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 33512072
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

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now