SQL Pivot?

Question

SQL Pivot?

ken.stoner

SSC Rookie

Points: 28
More actions
July 24, 2014 at 1:19 pm

#310306

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.

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

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

LoudClear SSC Eights! Points: 999 More actions · Answer 1

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

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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

ken.stoner SSC Rookie Points: 28 More actions · Answer 4

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

Thank you for all your help!

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 5

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)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 6

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')

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

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

,(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'')

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

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

,(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);