PIVOT Help

  • LutzM

    SSC Guru

    Points: 107049

    AlexT (5/14/2009)


    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

    Well, that looks similar to the solution posted in http://www.sqlservercentral.com/Forums/FindPost713749.aspx

    Doesn't it?;-)

    Edit: typo fixed. (Thanx Flo! And I say: Shame, shame, shame, shame, shame! Shame on me!!)



    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]

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    lmu92 (5/14/2009)


    Well, that looks similar to the solution posted in http://www.sqlservercentral.com/Forums/FindPost713749.aspx[

    Doesn't it?;-)

    Psst, Lutz. Remove the "[" at the end of your link 😉

Viewing 2 posts - 16 through 17 (of 17 total)

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