Dynamic Pivot

Question

Dynamic Pivot

bikram.g.it

Mr or Mrs. 500

Points: 533
More actions
July 28, 2015 at 8:38 pm

#306197

Hi,
I need to PIVOT a table of the following structure -
[id][firstName] [lastName]
1Fred Flintstone
2Barney Rubble
1Wilma Flintstone
3Betty Rubble
3Bam Bam Rubble
3Pebbles Rubble
to this -
[id][First Name 1][Last Name 1][First Name 2][Last Name 2] [First Name 3] [Last Name 3]
1Fred FlintstoneWilma Flintstone
2Barney Rubble
3Bam Bam Rubble Betty Rubble Pebbles Rubble
I attempted doing a dynamic PIVOT and messed it up completely :crying:. Looking for some ideas please :blink:

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

bikram.g.it (7/28/2015)
Hi,
I need to PIVOT a table of the following structure -
[id][firstName] [lastName]
1Fred Flintstone
2Barney Rubble
1Wilma Flintstone
3Betty Rubble
3Bam Bam Rubble
3Pebbles Rubble
to this -
[id][First Name 1][Last Name 1][First Name 2][Last Name 2] [First Name 3] [Last Name 3]
1Fred FlintstoneWilma Flintstone
2Barney Rubble
3Bam Bam Rubble Betty Rubble Pebbles Rubble
I attempted doing a dynamic PIVOT and messed it up completely :crying:. Looking for some ideas please :blink:

Please read the first link in my signature for how to post data where we can use it easier. It's a short sample data set, so I did it for you this time.

This query provides the desired result:

WITH SampleData AS

(

SELECT *,

ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt.lastName, dt.firstName) AS RN

FROM (VALUES (1, 'Fred', 'Flintstone'),

(2, 'Barney', 'Rubble'),

(1, 'Wilma', 'Flintstone'),

(3, 'Betty', 'Rubble'),

(3, 'Bam Bam', 'Rubble'),

(3, 'Pebbles', 'Rubble')

) dt(id, firstName, lastName)

)

SELECT id,

MAX(CASE WHEN RN = 1 THEN firstName ELSE NULL END) AS [First Name 1],

MAX(CASE WHEN RN = 1 THEN lastName ELSE NULL END) AS [Last Name 1],

MAX(CASE WHEN RN = 2 THEN firstName ELSE NULL END) AS [First Name 2],

MAX(CASE WHEN RN = 2 THEN lastName ELSE NULL END) AS [Last Name 2],

MAX(CASE WHEN RN = 3 THEN firstName ELSE NULL END) AS [First Name 3],

MAX(CASE WHEN RN = 3 THEN lastName ELSE NULL END) AS [Last Name 3]

FROM SampleData

GROUP BY id;

Your post title implies that you want this dynamic, and this is just coded for the three values. Please see the Cross Tabs and Pivot Tables, Part 1 and Part 2 links in my signature. Part 2 covers dynamic pivots.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

bikram.g.it Mr or Mrs. 500 Points: 533 More actions · Answer 2

Hi Wayne,

First up apologies for not posting the scripts for the test data creation.

And thanks for the tips for sharing those articles on Cross Tab & PIVOT. Although the articles said PIVOT is not the preferred I was a bit bullish on it and had to solve the problem using PIVOT. Which I have finally managed to do and realized how close I was before posting in the forum..

Thanks Anyway!