Post reply

Using Pivot grouped by several columns

  • September 29, 2010 at 3:21 pm

    #241029

    I am trying to use the pivot function but need the returned sums to be grouped by multiple columns, not just the pivot columns.

    Example:

    select year

    , school

    , level1

    , level2

    , level3

    , level4

    from (

    select year

    , school

    , level

    , sum(teachers) [teachers]

    from teachers

    group by year, school, level

    ) s

    PIVOT

    (

    sum(s.teachers)

    for s.level in ([level1],[level2],[level3],[level4])

    ) p

    This returns all null values, but the inner select returns just what is expected.

  • September 29, 2010 at 3:46 pm

    #1229378

    You might want to try the CrossTab method described in the related link in my signature.

    It's easier to read, usually performs better and is more flexible (easier to change to dynamic pivot).


    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]

Viewing 2 posts - 1 through 2 (of 2 total)

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