SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Pivot?


SQL Pivot?

Author
Message
ken.stoner
ken.stoner
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
LoudClear
LoudClear
SSC Veteran
SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)

Group: General Forum Members
Points: 282 Visits: 1247
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
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15172 Visits: 18602
Quick and simple
Cool

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
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16615 Visits: 19098
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
ken.stoner
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 10
The cross tab works exactly the way I want it too!

Thank you for all your help!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86428 Visits: 41098
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15172 Visits: 18602
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;-)
Cool
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);



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search