April 30, 2009 at 3:35 pm
I'm wondering if anyone knows how to use the PIVOT operator to solve this problem. I kept looking at it in BOL, could't figure out a way to use PIVOT and ended up solving it another way.
Table
CUST_NO PriceRule Discount
1234 I 2.30
1234 J 1.55
1234 K 0.99
1234 L 2.25
2345 I 2.30
2345 M 2.15
3456 K 0.99
There can only be up to 5 price rules per customer I would like a record set like this:
CUST_NO Rule1 Disc1 Rule2 Disc2 Rule3 Disc3 Rule4 Disc4 Rule5 Disc5
1234 I 2.30 J 1.55 K 0.99 L 2.25 NULL NULL
2345 I 2.30 M 2.15 NULL NULL NULL NULL NULL NULL
3456 K 0.99 NULL NULL NULL NULL NULL NULL NULL NULL
I don't need any aggregates - just a left to right pivot.
Todd Fifield
May 5, 2009 at 12:48 am
You can use the PIVOT operator to produce this output. To use the PIVOT operator, perform the following steps:
1. Select the data you need by using a special type of subquery called a derived table.
2. After you define the derived table, apply the PIVOT operator and specify an
aggregate function to use.
3. Define which columns you want to include in the output.
e.g.
SELECT [0], [1]
FROM
(
SELECT SalariedFlag, VacationHours
FROM HumanResources.Employee
) AS H
PIVOT
(
AVG(VacationHours)
FOR SalariedFlag IN ([0], [1])
) AS Pvt
May 10, 2009 at 10:51 am
Thanks for your response. This isn't quite what I needed, however. I wanted to get 1 row per customer, with up to 5 sets of ordered pairs. If the customer only had 1 price rule and discount, then Rule1 and Disc1 would be populated and the rest would just have nulls.
Any ideas?
Todd Fifield
May 10, 2009 at 11:13 am
Hi
It doesn't really look like a pivot for me. What about a concatenated column?
Greets
Flo
May 10, 2009 at 12:33 pm
Since the number of PriceRules has a known limit, the following proposal should work:
step 1: use ROW_NUMBER to identify the first, second a.s.o. PriceRule per CUST_NO
and step 2: select the values according to the position calculated before.
DECLARE @Table TABLE (CUST_NO INT, PriceRule CHAR(1), Discount numeric(8,2))
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
;WITH CTE_rowTable(id,CUST_NO , PriceRule, Discount)
AS(
SELECT ROW_NUMBER() OVER(PARTITION BY CUST_NO ORDER BY CUST_NO),
CUST_NO , PriceRule, Discount FROM @Table
)
SELECT CUST_NO,
MAX(CASE WHEN ID=1 THEN PriceRule ELSE null END) AS Rule1,
MAX(CASE WHEN ID=1 THEN Discount ELSE null END) AS Disc1,
MAX(CASE WHEN ID=2 THEN PriceRule ELSE null END) AS Rule2,
MAX(CASE WHEN ID=2 THEN Discount ELSE null END) AS Disc2,
MAX(CASE WHEN ID=3 THEN PriceRule ELSE null END) AS Rule3,
MAX(CASE WHEN ID=3 THEN Discount ELSE null END) AS Disc3,
MAX(CASE WHEN ID=4 THEN PriceRule ELSE null END) AS Rule4,
MAX(CASE WHEN ID=4 THEN Discount ELSE null END) AS Disc4,
MAX(CASE WHEN ID=5 THEN PriceRule ELSE null END) AS Rule5,
MAX(CASE WHEN ID=5 THEN Discount ELSE null END) AS Disc5
FROM CTE_rowTable
GROUP BY CUST_NO
/* Result set
CUST_NORule1Disc1Rule2Disc2Rule3Disc3Rule4Disc4Rule5Disc5
1234I2.30J1.55K0.99L2.25I2.30
2345I2.30M2.15I2.30M2.15NULLNULL
3456K0.99K0.99NULLNULLNULLNULLNULLNULL
*/
May 10, 2009 at 3:57 pm
Thanks guys. The CTE with Row Number was exactly how I did solve it. I just though there might be a way to use PIVOT to solve the problem.
Todd
May 11, 2009 at 12:27 am
Hi,
the reason why a Pivot cannot be used with the original data is that you're not referring to an existing element.
Example:
For CUST_NO 1234 and 2345 Rule1 = 'I' but for 3456 Rule 1='K'.
If you have a given number of rules (let's say 'A' to 'Z') you can use a Pivot function to get the discount per customer and rule.
Something like
CUST_NO A ... I J K L M Z
1234NULL... 2.30 1.55 0.99 2.25 NULL NULL
2345NULL... NULL NULL NULL NULL 2.15 NULL
3456NULL... NULL NULL 0.99 NULL NULL NULL
If you need the output as requested first you need to number the rules first. Probably you could use Pivot with the CTE result. I need to look into it.
May 11, 2009 at 9:09 am
Imu,
If you can come up with it, my hat is off to you.
Todd
May 11, 2009 at 1:16 pm
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
*/
May 11, 2009 at 3:12 pm
or you can do something really simple, like this
DECLARE @Table TABLE (CUST_NO INT, PriceRule CHAR(1), Discount numeric(8,2))
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.40 UNION ALL
SELECT 2345, 'M', 2.15 UNION ALL
SELECT 3456, 'K', 0.99
SELECT CUST_NO,
Char(sum(CASE WHEN PriceRule = 'I' THEN Ascii(PriceRule) ELSE '' END)) AS [Rule1],
SUM(CASE WHEN PriceRule = 'I' THEN Discount ELSE 0 END) AS [Disc1],
Char(sum(CASE WHEN PriceRule = 'J' THEN Ascii(PriceRule) ELSE '' END)) AS [Rule2],
SUM(CASE WHEN PriceRule = 'J' THEN Discount ELSE 0 END) AS [Disc2],
Char(sum(CASE WHEN PriceRule = 'K' THEN Ascii(PriceRule) ELSE '' END)) AS [Rule3],
SUM(CASE WHEN PriceRule = 'K' THEN Discount ELSE 0 END) AS [Disc3],
Char(sum(CASE WHEN PriceRule = 'L' THEN Ascii(PriceRule) ELSE '' END)) AS [Rule4],
SUM(CASE WHEN PriceRule = 'L' THEN Discount ELSE 0 END) AS [Disc4],
Char(sum(CASE WHEN PriceRule = 'M' THEN Ascii(PriceRule) ELSE '' END)) AS [Rule5],
SUM(CASE WHEN PriceRule = 'M' THEN Discount ELSE 0 END) AS [Disc5]
FROM @Table
GROUP BY CUST_NO
probably fustiest
May 11, 2009 at 6:17 pm
Lutz and Alex,
I really appreciate the time you guys put in on this problem. Unfortunately neither solution worked for this client. In the sample data for the table I didn't mention that the client may add more price rules like 'A' or 'Z'. The client wanted each price rule for a customer to go from left to right starting at the lowest letter for that customer. There are more than 5 different prices rules but each customer may only have 5 that apply to that customer. For that reason I needed to use the Rule1, Discount1, Rule2, Discount2 convention for the column names.
Here's what I actually did (I go up to 8 rules per customer since I never believe a client when they tell me 'There will only be 5'). There will always be at least 1 price rule for the customer or they wouldn't be in the table to start with:
IF OBJECT_ID('TempDb..#Table') IS NOT NULL
DROP TABLE #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
;WITH CPR AS (
SELECT
CUST_NO
, RowNum
, CASEWHEN RowNum = 1 THEN PriceRule END AS Rule1
, CASEWHEN RowNum = 1 THEN Discount END AS Disc1
, CASEWHEN RowNum = 2 THEN PriceRule END AS Rule2
, CASEWHEN RowNum = 2 THEN Discount END AS Disc2
, CASEWHEN RowNum = 3 THEN PriceRule END AS Rule3
, CASEWHEN RowNum = 3 THEN Discount END AS Disc3
, CASEWHEN RowNum = 4 THEN PriceRule END AS Rule4
, CASEWHEN RowNum = 4 THEN Discount END AS Disc4
, CASEWHEN RowNum = 5 THEN PriceRule END AS Rule5
, CASEWHEN RowNum = 5 THEN Discount END AS Disc5
, CASEWHEN RowNum = 6 THEN PriceRule END AS Rule6
, CASEWHEN RowNum = 6 THEN Discount END AS Disc6
, CASEWHEN RowNum = 7 THEN PriceRule END AS Rule7
, CASEWHEN RowNum = 7 THEN Discount END AS Disc7
, CASEWHEN RowNum = 8 THEN PriceRule END AS Rule8
, CASEWHEN RowNum = 8 THEN Discount END AS Disc8
FROM
(SELECT CUST_NO, PriceRule, Discount
, ROW_NUMBER() OVER (PARTITION BY CUST_NO ORDER BY PriceRule) AS RowNum
FROM #Table
) AS X
)
SELECT R1.CUST_NO, R1.Rule1, R1.Disc1, R2.Rule2, R2.Disc2, R3.Rule3, R3.Disc3
, R4.Rule4, R4.Disc4, R5.Rule5, R5.Disc5, R6.Rule6, R6.Disc6
, R7.Rule7, R7.Disc7, R8.Rule8, R8.Disc8
FROM
(SELECT CUST_NO, Rule1, Disc1 FROM CPR WHERE RowNum = 1) AS R1
LEFT JOIN
(SELECT CUST_NO, Rule2, Disc2 FROM CPR WHERE RowNum = 2) AS R2
ON R1.CUST_NO = R2.CUST_NO
LEFT JOIN
(SELECT CUST_NO, Rule3, Disc3 FROM CPR WHERE RowNum = 3) AS R3
ON R1.CUST_NO = R3.CUST_NO
LEFT JOIN
(SELECT CUST_NO, Rule4, Disc4 FROM CPR WHERE RowNum = 4) AS R4
ON R1.CUST_NO = R4.CUST_NO
LEFT JOIN
(SELECT CUST_NO, Rule5, Disc5 FROM CPR WHERE RowNum = 5) AS R5
ON R1.CUST_NO = R5.CUST_NO
LEFT JOIN
(SELECT CUST_NO, Rule6, Disc6 FROM CPR WHERE RowNum = 6) AS R6
ON R1.CUST_NO = R6.CUST_NO
LEFT JOIN
(SELECT CUST_NO, Rule7, Disc7 FROM CPR WHERE RowNum = 7) AS R7
ON R1.CUST_NO = R7.CUST_NO
LEFT JOIN
(SELECT CUST_NO, Rule8, Disc8 FROM CPR WHERE RowNum = 8) AS R8
ON R1.CUST_NO = R8.CUST_NO
May 12, 2009 at 12:20 am
I just used the CTE sample code I provided in post http://www.sqlservercentral.com/Forums/FindPost714447.aspx and
added the following lines to the table, without modifying the CTE's:
INSERT into #Table
SELECT 3456, 'Z', 2.30 UNION ALL
SELECT 1234, 'H', 2.30
/* Result set
1234H2.30I2.30J1.55K0.99L2.25
2345I2.30M2.15NULLNULLNULLNULLNULLNULL
3456K0.99Z2.30NULLNULLNULLNULLNULLNULL
*/
If you feel that the code I provided didn't work as you expected I'd like you to provide the sample data that failed together with the result based on the code I provided together with the expected result.
Other than that I don't think I can help you...
May 12, 2009 at 1:33 pm
Lutz,
I must not have been looking closely at the screen when I ran the test from your previous post. You're right!!!
That was just bloody brilliant. Thanks!! I kind of figured that there should be some way to use PIVOT to do what I wanted. I guess I just didn't have the patience to work it all the way through.
Thanks again!
Todd
May 12, 2009 at 5:30 pm
I'm glad I could be of some help!
Even though Microsoft claims that the PIVOT function is kind'a easy to handle it still takes some time/effort/experience/t&e to finally get it to run... I wouldn't consider this function to have user friendly syntax...
And, to be honest, I haven't used PIVOT to include more than one group before so it was a learning experience for me as well...;-)
Edit: typo fix
May 14, 2009 at 7:42 am
Yes, very interesting point of view, but syntax is looks very complicated.
old style do the same and looks much clearer
DECLARE @Table TABLE (CUST_NO INT, PriceRule CHAR(1), Discount numeric(8,2))
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.40 UNION ALL
SELECT 2345, 'M', 2.15 UNION ALL
SELECT 2345, 'N', 1.15 UNION ALL
SELECT 3456, 'K', 0.99
;with t(i, CUST_NO, PriceRule , Discount ) as(
SELECT ROW_NUMBER() OVER(Partition by CUST_NO ORDER BY CUST_NO,PriceRule),CUST_NO,
PriceRule , Discount
FROM @Table)
SELECT CUST_NO,
max(CASE WHEN i = 1 THEN PriceRule ELSE '' END) AS [Rule1],
SUM(CASE WHEN i = i THEN Discount ELSE 0 END) AS [Disc1],
max(CASE WHEN i = 2 THEN PriceRule ELSE '' END) AS [Rule2],
SUM(CASE WHEN i = 2 THEN Discount ELSE 0 END) AS [Disc2],
max(CASE WHEN i = 3 THEN PriceRule ELSE '' END) AS [Rule3],
SUM(CASE WHEN i = 3 THEN Discount ELSE 0 END) AS [Disc3],
max(CASE WHEN i = 4 THEN PriceRule ELSE '' END) AS [Rule4],
SUM(CASE WHEN i = 4 THEN Discount ELSE 0 END) AS [Disc4],
max(CASE WHEN i = 5 THEN PriceRule ELSE '' END) AS [Rule5],
SUM(CASE WHEN i = 5 THEN Discount ELSE 0 END) AS [Disc5]
FROM T
GROUP BY CUST_NO
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply