SQL Pivot?

  • I have data like:

    ItemIdCustomerItemIdCustomerPlantUnitPriceCustomerPO

    63782604029-5C1P10.7208P1000

    63782604029-5C2P10.6889441670

    63782604029-5C2P10.6889411754

    63782604029-5C2P10.6889412530

    I need it like this:

    ItemIdCustomerItemIdCustomerPlantUnitPriceCustomerPOCustomerItemIdCustomerPlantUnitPriceCustomerPOCustomerItemIdCustomerPlantUnitPriceCustomerPOCustomerItemIdCustomerPlantUnitPriceCustomerPO

    63782604029-5C1P10.7208P100082604029-5C2P10.688944167082604029-5C2P10.688941175482604029-5C2P10.6889412530

    I can't seem to figure out the best way to do this. note that there are a bunch of ItemIDs and the number of rows for each is variable.

  • Hi Ken -

    HOW variable is the data? I think this impacts the solution. Your example data currently results in 21 columns. What's the maximum # of rows per ItemID in the input? Would you always want all of them in one huge output row?

    - Adam

  • Quick and simple

    😎

    USE tempdb;

    GO

    /* LETS PRETEND THIS IS THE TABLE */

    ;WITH BASE_DATA AS

    (

    SELECT

    ItemId

    ,CustomerItemId

    ,Customer

    ,Plant

    ,UnitPrice

    ,CustomerPO

    FROM (VALUES

    (637,'82604029-5','C1','P1','0.7208','P1000')

    ,(637,'82604029-5','C2','P1','0.6889','441670')

    ,(637,'82604029-5','C2','P1','0.6889','411754')

    ,(637,'82604029-5','C2','P1','0.6889','412530')

    ) AS X(ItemId,CustomerItemId,Customer,Plant,UnitPrice,CustomerPO)

    )

    /* AND THIS IS OUR CTE */

    ,RN_DATA AS

    (

    SELECT

    ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY (SELECT NULL)) AS BD_RID

    ,BD.ItemId

    ,BD.CustomerItemId

    ,BD.Customer

    ,BD.Plant

    ,BD.UnitPrice

    ,BD.CustomerPO

    FROM BASE_DATA BD

    )

    SELECT

    RD1.ItemId

    ,RD1.CustomerItemId

    ,RD1.Customer

    ,RD1.Plant

    ,RD1.UnitPrice

    ,RD1.CustomerPO

    ,RD2.CustomerItemId

    ,RD2.Customer

    ,RD2.Plant

    ,RD2.UnitPrice

    ,RD2.CustomerPO

    ,RD3.CustomerItemId

    ,RD3.Customer

    ,RD3.Plant

    ,RD3.UnitPrice

    ,RD3.CustomerPO

    ,RD4.CustomerItemId

    ,RD4.Customer

    ,RD4.Plant

    ,RD4.UnitPrice

    ,RD4.CustomerPO

    FROM RN_DATA RD1

    OUTER APPLY RN_DATA RD2

    OUTER APPLY RN_DATA RD3

    OUTER APPLY RN_DATA RD4

    WHERE RD1.ItemId = RD2.ItemId

    AND RD2.ItemId = RD3.ItemId

    AND RD3.ItemId = RD4.ItemId

    AND RD1.BD_RID = RD2.BD_RID - 1

    AND RD2.BD_RID = RD3.BD_RID - 1

    AND RD3.BD_RID = RD4.BD_RID - 1

    Results

    ItemId CustomerItemId Customer Plant UnitPrice CustomerPO CustomerItemId Customer Plant UnitPrice CustomerPO CustomerItemId Customer Plant UnitPrice CustomerPO CustomerItemId Customer Plant UnitPrice CustomerPO

    ----------- -------------- -------- ----- --------- ---------- -------------- -------- ----- --------- ---------- -------------- -------- ----- --------- ---------- -------------- -------- ----- --------- ----------

    637 82604029-5 C1 P1 0.7208 P1000 82604029-5 C2 P1 0.6889 441670 82604029-5 C2 P1 0.6889 411754 82604029-5 C2 P1 0.6889 412530

  • A cross tabs approach using Eirikur's sample data.

    WITH BASE_DATA AS

    (

    SELECT

    ItemId

    ,CustomerItemId

    ,Customer

    ,Plant

    ,UnitPrice

    ,CustomerPO

    FROM (VALUES

    (637,'82604029-5','C1','P1','0.7208','P1000')

    ,(637,'82604029-5','C2','P1','0.6889','441670')

    ,(637,'82604029-5','C2','P1','0.6889','411754')

    ,(637,'82604029-5','C2','P1','0.6889','412530')

    ) AS X(ItemId,CustomerItemId,Customer,Plant,UnitPrice,CustomerPO)

    )

    /* AND THIS IS OUR CTE */

    ,RN_DATA AS

    (

    SELECT

    ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY (SELECT NULL)) AS BD_RID

    ,BD.ItemId

    ,BD.CustomerItemId

    ,BD.Customer

    ,BD.Plant

    ,BD.UnitPrice

    ,BD.CustomerPO

    FROM BASE_DATA BD

    )

    SELECT

    RD.ItemId

    ,MAX(CASE WHEN RD.BD_RID = 1 THEN RD.CustomerItemId END) AS CustomerItemId1

    ,MAX(CASE WHEN RD.BD_RID = 1 THEN RD.Customer END) AS Customer1

    ,MAX(CASE WHEN RD.BD_RID = 1 THEN RD.Plant END) AS Plant1

    ,MAX(CASE WHEN RD.BD_RID = 1 THEN RD.UnitPrice END) AS UnitPrice1

    ,MAX(CASE WHEN RD.BD_RID = 1 THEN RD.CustomerPO END) AS CustomerPO1

    ,MAX(CASE WHEN RD.BD_RID = 2 THEN RD.CustomerItemId END) AS CustomerItemId2

    ,MAX(CASE WHEN RD.BD_RID = 2 THEN RD.Customer END) AS Customer2

    ,MAX(CASE WHEN RD.BD_RID = 2 THEN RD.Plant END) AS Plant2

    ,MAX(CASE WHEN RD.BD_RID = 2 THEN RD.UnitPrice END) AS UnitPrice2

    ,MAX(CASE WHEN RD.BD_RID = 2 THEN RD.CustomerPO END) AS CustomerPO2

    ,MAX(CASE WHEN RD.BD_RID = 3 THEN RD.CustomerItemId END) AS CustomerItemId3

    ,MAX(CASE WHEN RD.BD_RID = 3 THEN RD.Customer END) AS Customer3

    ,MAX(CASE WHEN RD.BD_RID = 3 THEN RD.Plant END) AS Plant3

    ,MAX(CASE WHEN RD.BD_RID = 3 THEN RD.UnitPrice END) AS UnitPrice3

    ,MAX(CASE WHEN RD.BD_RID = 3 THEN RD.CustomerPO END) AS CustomerPO3

    ,MAX(CASE WHEN RD.BD_RID = 4 THEN RD.CustomerItemId END) AS CustomerItemId4

    ,MAX(CASE WHEN RD.BD_RID = 4 THEN RD.Customer END) AS Customer4

    ,MAX(CASE WHEN RD.BD_RID = 4 THEN RD.Plant END) AS Plant4

    ,MAX(CASE WHEN RD.BD_RID = 4 THEN RD.UnitPrice END) AS UnitPrice4

    ,MAX(CASE WHEN RD.BD_RID = 4 THEN RD.CustomerPO END) AS CustomerPO4

    FROM RN_DATA RD

    GROUP BY RD.ItemId

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • The cross tab works exactly the way I want it too!

    Thank you for all your help!

  • ken.stoner (7/25/2014)


    The cross tab works exactly the way I want it too!

    Thank you for all your help!

    Until you get a 5th row. 😉 Then, you'd need to convert to dynamic SQL.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (7/25/2014)


    ken.stoner (7/25/2014)


    The cross tab works exactly the way I want it too!

    Thank you for all your help!

    Until you get a 5th row. 😉 Then, you'd need to convert to dynamic SQL.

    In this case the dynamic SQL is straight forward as it only has to repeat the same chunk of code for each row. Here is a quick demonstration. Note that the "source" table variable is both within and outside the dynamic SQL. These would of course be replaced by a single table;-)

    😎

    USE tempdb;

    GO

    DECLARE @SQL_STR NVARCHAR(MAX) = N'';

    ;WITH BASE_DATA AS

    (

    SELECT

    ItemId

    ,CustomerItemId

    ,Customer

    ,Plant

    ,UnitPrice

    ,CustomerPO

    FROM (VALUES

    (637,'82604029-5','C1','P1','0.7208','P1000')

    ,(637,'82604029-5','C2','P1','0.6889','441670')

    ,(637,'82604029-5','C2','P1','0.6889','411754')

    ,(637,'82604029-5','C2','P1','0.6889','412530')

    ,(637,'82604029-5','C2','P1','0.6889','412530')

    ,(637,'82604029-5','C2','P1','0.6889','412530')

    ,(638,'82604029-5','C2','P1','0.6889','441670')

    ,(638,'82604029-5','C2','P1','0.6889','411754')

    ,(638,'82604029-5','C2','P1','0.6889','412530')

    ,(638,'82604029-5','C2','P1','0.6889','412530')

    ,(638,'82604029-5','C2','P1','0.6889','412530')

    ) AS X(ItemId,CustomerItemId,Customer,Plant,UnitPrice,CustomerPO)

    )

    /* HOW MANY GROUPS? */

    ,NUMBER_OF_GROUPS AS

    (

    SELECT MAX(GR_COUNT.ITEM_ID_COUNT) AS MX_GR_COUNT

    FROM

    (

    SELECT COUNT(ItemId) OVER (PARTITION BY ItemId) AS ITEM_ID_COUNT

    FROM BASE_DATA

    ) AS GR_COUNT

    )

    /* TALLY THINGY */

    ,T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

    SELECT @SQL_STR = N'

    ;WITH BASE_DATA AS

    (

    SELECT

    ItemId

    ,CustomerItemId

    ,Customer

    ,Plant

    ,UnitPrice

    ,CustomerPO

    FROM (VALUES

    (637,''82604029-5'',''C1'',''P1'',''0.7208'',''P1000'')

    ,(637,''82604029-5'',''C2'',''P1'',''0.6889'',''441670'')

    ,(637,''82604029-5'',''C2'',''P1'',''0.6889'',''411754'')

    ,(637,''82604029-5'',''C2'',''P1'',''0.6889'',''412530'')

    ,(637,''82604029-5'',''C2'',''P1'',''0.6889'',''412530'')

    ,(637,''82604029-5'',''C2'',''P1'',''0.6889'',''412530'')

    ,(638,''82604029-5'',''C2'',''P1'',''0.6889'',''441670'')

    ,(638,''82604029-5'',''C2'',''P1'',''0.6889'',''411754'')

    ,(638,''82604029-5'',''C2'',''P1'',''0.6889'',''412530'')

    ,(638,''82604029-5'',''C2'',''P1'',''0.6889'',''412530'')

    ,(638,''82604029-5'',''C2'',''P1'',''0.6889'',''412530'')

    ) AS X(ItemId,CustomerItemId,Customer,Plant,UnitPrice,CustomerPO)

    )

    /* AND THIS IS OUR CTE */

    ,RN_DATA AS

    (

    SELECT

    ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY (SELECT NULL)) AS BD_RID

    ,BD.ItemId

    ,BD.CustomerItemId

    ,BD.Customer

    ,BD.Plant

    ,BD.UnitPrice

    ,BD.CustomerPO

    FROM BASE_DATA BD

    )

    SELECT

    RD.ItemId

    ' + (

    SELECT REPLACE(N'

    ,MAX(CASE WHEN RD.BD_RID = $ THEN RD.CustomerItemId END) AS CustomerItemId$

    ,MAX(CASE WHEN RD.BD_RID = $ THEN RD.Customer END) AS Customer$

    ,MAX(CASE WHEN RD.BD_RID = $ THEN RD.Plant END) AS Plant$

    ,MAX(CASE WHEN RD.BD_RID = $ THEN RD.UnitPrice END) AS UnitPrice$

    ,MAX(CASE WHEN RD.BD_RID = $ THEN RD.CustomerPO END) AS CustomerPO$

    ',N'$',NM.N)

    FROM NUMBER_OF_GROUPS NOG

    OUTER APPLY

    (

    SELECT TOP (NOG.MX_GR_COUNT) CONVERT(NVARCHAR(12),ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),0) AS N

    FROM T T1,T T2,T T3,T T4

    ) AS NM

    FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(MAX)')

    + N'

    FROM RN_DATA RD

    GROUP BY RD.ItemId

    ';

    EXEC (@SQL_STR);

Viewing 7 posts - 1 through 6 (of 6 total)

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