Home Forums SQL Server 2008 SQL Server 2008 - General Transpose Rows to Columns with first column values as column names from the table?? RE: Transpose Rows to Columns with first column values as column names from the table??

  • Looking at the original query it is very de-normalized and almost looks like a pivot table in its own right so the first thing i did to accomplish the task was to UNPIVOT the data (i used a cross apply) First i generated some sample data

    CREATE TABLE SampleData (

    EffectiveDate DATE,

    CanDrinks INT, --ints are easy to generate

    Bottle20CSD INT,

    Snacks INT,

    Coffee INT,

    Food INT

    --..... this gives enough columns to get the point accross

    )

    INSERT INTO SampleData

    SELECT DATEADD(DD,(ABS(CHECKSUM(NEWID())) % 25) * -1,GETDATE()),

    ABS(CHECKSUM(NEWID())) % 1000,

    ABS(CHECKSUM(NEWID())) % 1000,

    ABS(CHECKSUM(NEWID())) % 1000,

    ABS(CHECKSUM(NEWID())) % 1000,

    ABS(CHECKSUM(NEWID())) % 1000

    FROM Tally

    WHERE N < 20

    ;WITH Dupes AS (SELECT ROW_NUMBER() OVER (PARTITION BY EffectiveDate ORDER BY CanDrinks) AS RN, *

    FROM SampleData)

    DELETE FROM Dupes WHERE RN > 1

    SELECT * FROM SampleData

    Then with data that is close to what i think yours is i normalized the data on the fly with

    SELECT EffectiveDate, ItemName, Ammount

    FROM SampleData

    CROSS APPLY (VALUES ('CanDrinks',CanDrinks),('Bottle20CSD',Bottle20CSD),('Snacks',Snacks),('Coffee',Coffee),('Food',Food))x(ItemName,Ammount)

    This will allow us to make a nice pivot table in the output you want. With the unpivot you can then write your dynamic pivot table (or cross tab) in the same manor you were attacking the problem before. if you have any other questions let me know.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]