Dynamic Pivot

  • 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:

  • 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

  • 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!

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

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