Post reply

Combining results into one data row

  • August 22, 2011 at 2:21 am

    #255907

    Hi

    I've developed a stored proc in SQL 2005 that produces results for each customer based on various aggregate queries, the data being combined with UNION statements and put into a temporary table. The results look something like:

    CustomerID DataLabel TotalSpend TotalOrderQuantity

    1 'ThisYear' 1234 745

    1 'LastYear' 588 450

    1 'LastYearYTD' 490 300

    2 'ThisYear' 245 123

    etc, etc,

    Is there a way that the results can be combined together easily in one row? I'm fairly sure PIVOT will only work on one of the values at a time.

    Any suggestions please?!? All help gratefully received!

    thanks

    F

  • August 22, 2011 at 7:45 am

    #1371409

    Please have a look at the CrossTab article referenced in my signature. It's most probably exactly what you're looking for.

    As a side note: If you know the data of each UNION statement will be unique (e.g. due to the different DataLabel values), you should use UNION ALL instead of UNION to avoid the additional grouping overhead.


    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]

  • August 22, 2011 at 8:36 am

    #1371446

    Thanks! Looks exactly like what I need 🙂

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

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