Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Pivot? Expand / Collapse
Author
Message
Posted Thursday, July 24, 2014 1:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:46 PM
Points: 2, Visits: 10
I have data like:

ItemId	CustomerItemId	Customer	Plant	UnitPrice	CustomerPO
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

I need it like this:

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


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.
Post #1596001
Posted Thursday, July 24, 2014 1:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 10:04 AM
Points: 147, Visits: 365
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
Post #1596009
Posted Thursday, July 24, 2014 1:53 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:18 AM
Points: 2,560, Visits: 7,192
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
Post #1596010
Posted Thursday, July 24, 2014 2:20 PM This worked for the OP Answer marked as solution


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 26, 2014 11:13 AM
Points: 4,067, Visits: 9,235
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1596017
Posted Friday, July 25, 2014 6:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:46 PM
Points: 2, Visits: 10
The cross tab works exactly the way I want it too!

Thank you for all your help!
Post #1596181
Posted Friday, July 25, 2014 12:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:38 PM
Points: 35,858, Visits: 32,529
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1596343
Posted Friday, July 25, 2014 11:41 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:18 AM
Points: 2,560, Visits: 7,192
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);


Post #1596455
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse