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