Post reply

Please help with a simple query, I need to group data in a certain way (example available)

  • December 24, 2011 at 2:18 pm

    #265357

    Hello,

    My query is:

    select ID, Size from TableName

    The results are:

    And I need the output to be like this:

    I would prefer doing this without making any temporary tables.

    I appreciate any help. Thanks!

  • December 24, 2011 at 2:37 pm

    #1425367

    Using PIVOT and UNPIVOT


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • December 24, 2011 at 3:33 pm

    #1425373

    I'd rather use the good old CrossTab approach as described in the related link in my signature.

    It'd not only easier to remember but usually also faster than the 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]

  • December 27, 2011 at 7:09 pm

    #1425912

    I second LutzM's crosstab suggestion as I find it easier than pivot as well. One tip for after you read the article he suggested if you go the crosstab route: Use MAX(case when ...) instead of SUM(case when ...).

    Good luck and nice problem.

  • December 27, 2011 at 9:01 pm

    #1425928

    select ID,

    (case when [Large] > 0 then 'Yes' else 'No' end)[Large],

    (case when [Small] > 0 then 'Yes' else 'No' end)[Small]

    from ##TableName a

    pivot

    (

    count(Size)

    for Size in ([Large], [Small])

    ) b

    "Often speak with code not with word,
    A simple solution for a simple question"

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

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