# PIVOT Help

• 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

• 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

• 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

• Hi

It doesn't really look like a pivot for me. What about a concatenated column?

Greets

Flo

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

• 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_NO Rule1 Disc1 Rule2 Disc2 Rule3 Disc3 Rule4 Disc4 Rule5 Disc5 1234 I 2.30 J 1.55 K 0.99 L 2.25 I 2.30 2345 I 2.30 M 2.15 I 2.30 M 2.15 NULL NULL 3456 K 0.99 K 0.99 NULL NULL NULL NULL NULL NULL */ ```

Lutz
A pessimist is an optimist with experience.

How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• 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

• 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

1234 NULL... 2.30 1.55 0.99 2.25 NULL NULL

2345 NULL... NULL NULL NULL NULL 2.15 NULL

3456 NULL... 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.

Lutz
A pessimist is an optimist with experience.

How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Imu,

If you can come up with it, my hat is off to you.

Todd

• 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 1234 2.30 1.55 0.99 2.25 NULL 2345 2.30 NULL NULL NULL 2.15 3456 NULL NULL 0.99 NULL NULL ```

`*/`

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_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 ```

`*/`

Lutz
A pessimist is an optimist with experience.

How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• 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

• 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 , CASE WHEN RowNum = 1 THEN PriceRule END AS Rule1 , CASE WHEN RowNum = 1 THEN Discount END AS Disc1 , CASE WHEN RowNum = 2 THEN PriceRule END AS Rule2 , CASE WHEN RowNum = 2 THEN Discount END AS Disc2 , CASE WHEN RowNum = 3 THEN PriceRule END AS Rule3 , CASE WHEN RowNum = 3 THEN Discount END AS Disc3 , CASE WHEN RowNum = 4 THEN PriceRule END AS Rule4 , CASE WHEN RowNum = 4 THEN Discount END AS Disc4 , CASE WHEN RowNum = 5 THEN PriceRule END AS Rule5 , CASE WHEN RowNum = 5 THEN Discount END AS Disc5 , CASE WHEN RowNum = 6 THEN PriceRule END AS Rule6 , CASE WHEN RowNum = 6 THEN Discount END AS Disc6 , CASE WHEN RowNum = 7 THEN PriceRule END AS Rule7 , CASE WHEN RowNum = 7 THEN Discount END AS Disc7 , CASE WHEN RowNum = 8 THEN PriceRule END AS Rule8 , CASE WHEN 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 ```

• 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 1234 H 2.30 I 2.30 J 1.55 K 0.99 L 2.25 2345 I 2.30 M 2.15 NULL NULL NULL NULL NULL NULL 3456 K 0.99 Z 2.30 NULL NULL NULL NULL NULL NULL ```

`*/`

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.

Lutz
A pessimist is an optimist with experience.

How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• 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

• 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

Lutz
A pessimist is an optimist with experience.

How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• 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 17 total)