PIVOT Help

  • tfifield

    SSCrazy Eights

    Points: 9655

    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

  • malaytech2008

    Valued Member

    Points: 65

    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

  • tfifield

    SSCrazy Eights

    Points: 9655

    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

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    Hi

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

    Greets

    Flo

  • LutzM

    SSC Guru

    Points: 107049

    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 get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • tfifield

    SSCrazy Eights

    Points: 9655

    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

  • LutzM

    SSC Guru

    Points: 107049

    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 get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • tfifield

    SSCrazy Eights

    Points: 9655

    Imu,

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

    Todd

  • LutzM

    SSC Guru

    Points: 107049

    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 get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • AlexT-359331

    SSC Veteran

    Points: 219

    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

  • tfifield

    SSCrazy Eights

    Points: 9655

    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

  • LutzM

    SSC Guru

    Points: 107049

    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.

    Other than that I don't think I can help you...



    Lutz
    A pessimist is an optimist with experience.

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

  • tfifield

    SSCrazy Eights

    Points: 9655

    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

  • LutzM

    SSC Guru

    Points: 107049

    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 get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • AlexT-359331

    SSC Veteran

    Points: 219

    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)

You must be logged in to reply to this topic. Login to reply