Post reply

Tricky pivot

  • July 26, 2010 at 12:25 pm

    #225390

    Hello all

    I have an interesting pivot I'm trying to tackle. First, here's a script to setup the data to work with

    declare @Items table(ID int,

    Code varchar(5),

    Value int,

    Amount int)

    insert @Items

    select 2011, 49, 6000, 2000 union all

    select 2011, 4600, 1000, 500 union all

    select 2011, 49, 6000, 2200 union all

    select 2011, 4600, 1000, 600 union all

    select 2012, 53, 6500, 3000 union all

    select 2012, 5000, 1300, 700 union all

    select 2012, 7300, 2000, 800 union all

    select 2012, 53, 6500, 3400 union all

    select 2012, 5000, 1300, 750 union all

    select 2012, 7300, 2000, 900 union all

    select 2012, 53, 6500, 4800 union all

    select 2012, 5000, 1300, 1000 union all

    select 2012, 7300, 2000, 1100

    I'm trying to pivot this data so it looks like this...

    /* Result

    ID Code1 Value1 Amount1 Code2 Value2 Amount2 Code3 Value3 Amount3 ... Code20 Value20 Amount20

    2011 49 6000 4200 4600 1000 1100 null null null

    2012 53 6500 11200 5000 1300 2450 7300 2000 2800

    */

    Each ID needs to list all of the Codes that are used (up to 20), the value for the code, and the sum of the amounts for that code. I don't think cross tabs will work for this because there are a lot of Codes (~4000). So I'm thinking I need to do an aggregate and then multiple pivots.

    Any suggestions on how to get this done?

    Thanks!

  • July 26, 2010 at 12:44 pm

    #1198386

    have a read of the following two articles..hopefully it will give you some ideas

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • July 26, 2010 at 1:01 pm

    #1198402

    Yes, I did take a look at those articles. What make this one more difficult is there are 4000 Codes and each Code could appear anywhere from Code1-Code20. That would be one crazy case statement if cross tabs were used. So I think the only option is to use the pivot command.

    But trying to get this to work using pivot is very tricky because the ID, Code and Value need to pivot together and then have their Amounts summed.

    Any other suggestion?

    Thanks!

  • July 26, 2010 at 5:45 pm

    #1198499

    I made a slight change ... we need something specific to order our items by. Now it may be that you want them in order of the Code value, but I'm guessing it's something chronological.

    declare @Items table

    (SomeObjectiveOrder int identity

    ,ID int

    ,Code varchar(5)

    ,Value int

    ,Amount int)

    insert @Items

    select 2011, '49', 6000, 2000 union all

    select 2011, '4600', 1000, 500 union all

    select 2011, '49', 6000, 2200 union all

    select 2011, '4600', 1000, 600 union all

    select 2012, '53', 6500, 3000 union all

    select 2012, '5000', 1300, 700 union all

    select 2012, '7300', 2000, 800 union all

    select 2012, '53', 6500, 3400 union all

    select 2012, '5000', 1300, 750 union all

    select 2012, '7300', 2000, 900 union all

    select 2012, '53', 6500, 4800 union all

    select 2012, '5000', 1300, 1000 union all

    select 2012, '7300', 2000, 1100

    The number of possible codes doesn't matter ... you just want codes 1-20 for each ID. In this example I'm pretending we're just looking for the first 2 codes. You should be able to use this example and extend it to the 20 you want.

    ;WITH cteItems AS

    (select ID,

    Code,

    MIN(Value) Value,

    SUM(Amount) Amount,

    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY MIN(SomeObjectiveOrder)) CodeOrder

    from @Items

    group by ID, Code)

    SELECT ID,

    MAX(CASE CodeOrder WHEN 1 THEN Code END) Code1,

    MAX(CASE CodeOrder WHEN 1 THEN Value END) Value1,

    MAX(CASE CodeOrder WHEN 1 THEN Amount END) Amount1,

    MAX(CASE CodeOrder WHEN 2 THEN Code END) Code2,

    MAX(CASE CodeOrder WHEN 2 THEN Value END) Value2,

    MAX(CASE CodeOrder WHEN 2 THEN Amount END) Amount2

    FROM cteItems

    WHERE CodeOrder <= 2

    GROUP BY ID

    ORDER BY ID

    Good luck.

    *edit: typo

    └> bt


    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • July 26, 2010 at 5:59 pm

    #1198500

    That's excellent!! Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

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