Hi Todd,
I used the first example from BOL (see "Using PIVOT and UNPIVOT") and modified it slightly to match the column names in order to get the following result without CTE.
-- create test table
create table #Table (CUST_NO INT, PriceRule CHAR(1), Discount numeric(8,2))
-- insert sample data
INSERT into #Table
SELECT 1234, 'I', 2.30 UNION ALL
SELECT 1234, 'J', 1.55 UNION ALL
SELECT 1234, 'K', 0.99 UNION ALL
SELECT 1234, 'L', 2.25 UNION ALL
SELECT 2345, 'I', 2.30 UNION ALL
SELECT 2345, 'M', 2.15 UNION ALL
SELECT 3456, 'K', 0.99
--
--
-- sample code without CTE
SELECT CUST_NO, AS I, [J] AS J, [K] AS K, [L] AS L, [M] AS M
FROM
(SELECT CUST_NO , PriceRule , Discount
FROM #Table) p
PIVOT
(
SUM (Discount)
FOR PriceRule IN
( , [J], [K], [L], [M] )
) AS pvt
ORDER BY CUST_NO
/* Result set
CUST_NO I J K L M
12342.301.550.992.25NULL
23452.30NULLNULLNULL2.15
3456NULLNULL0.99NULLNULL
*/
And here's the PIVOT solution based on the CTE:
Note: In order to show both, rule and discount, I used two separate CTE's, both based on the numbered PriceRules CTE.
-- sample code with CTE
;WITH CTE_RowNum(CUST_NO , PriceID, PriceRule , Discount) AS(
SELECT CUST_NO ,
ROW_NUMBER() OVER(Partition by CUST_NO ORDER BY CUST_NO,PriceRule),
PriceRule , Discount
FROM #Table)
,CTE_PivDisc (CUST_NO, D1,D2,D3,D4,D5) AS(
SELECT CUST_NO, [1], [2], [3], [4], [5]
FROM (
SELECT PriceID, CUST_NO , PriceRule , Discount
FROM CTE_RowNum
) p
PIVOT (
SUM (Discount)
FOR PriceId IN
( [1], [2], [3], [4], [5] )
) AS pvt)
, CTE_PivRule (CUST_NO, R1,R2,R3,R4,R5) AS(
SELECT CUST_NO, [1], [2], [3], [4], [5]
FROM (
SELECT PriceID, CUST_NO , PriceRule , Discount
FROM CTE_RowNum
) p
PIVOT (
MAX (PriceRule)
FOR PriceId IN
( [1], [2], [3], [4], [5] )
) AS pvt)
SELECT CTE_PivRule.cust_no,
MAX(R1) Rule1,MAX(D1) Disc1,
MAX(R2) Rule2,MAX(D2) Disc2,
MAX(R3) Rule3,MAX(D3) Disc3,
MAX(R4) Rule4,MAX(D4) Disc4,
MAX(R5) Rule5,MAX(D5) Disc5
FROM CTE_PivRule
LEFT OUTER JOIN CTE_PivDisc
ON CTE_PivRule.cust_no = CTE_PivDisc.cust_no
GROUP BY CTE_PivRule.cust_no
/* Result set:
cust_noRule1Disc1Rule2Disc2Rule3Disc3Rule4Disc4Rule5Disc5
1234I2.30J1.55K0.99L2.25NULLNULL
2345I2.30M2.15NULLNULLNULLNULLNULLNULL
3456K0.99NULLNULLNULLNULLNULLNULLNULLNULL
*/