Post reply

Pivot Query with 2 aggregated functions combined?

  • February 21, 2011 at 2:08 pm

    #231566

    Before anyone asks, I did use search before deciding to post this but I didn't find anything to solve what I was looking for.

    I have data that I want to see in the tabular format (hence the Pivot hehe) but the issue I'm having is I've been unable to have the aggregate column contain the data as I need to present it. I'm attempting to combine two different aggregated functions (MIN and MAX) in the pivot with a divider in the middle of each function ("~")

    Here is the code I use in a non-tabular format and I wish to keep the format I'm trying to get the same in the Pivot query.

    CONVERT(VARCHAR(3),MIN(InDay)) + '~' + CONVERT(VARCHAR(3),MAX(InDay))

    So it would look like 1~4 or 8~8

    Any suggestion? Or additional information you need please let me know

  • February 21, 2011 at 2:15 pm

    #1289612

    Darundo (2/21/2011)

    ...

    Any suggestion? Or additional information you need please let me know

    Please provide table definition, sample data and expected results based on those sample.


    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]

  • February 21, 2011 at 3:34 pm

    #1289638

    Darundo (2/21/2011)

    Before anyone asks, I did use search before deciding to post this but I didn't find anything to solve what I was looking for.

    I have data that I want to see in the tabular format (hence the Pivot hehe) but the issue I'm having is I've been unable to have the aggregate column contain the data as I need to present it. I'm attempting to combine two different aggregated functions (MIN and MAX) in the pivot with a divider in the middle of each function ("~")

    Here is the code I use in a non-tabular format and I wish to keep the format I'm trying to get the same in the Pivot query.

    CONVERT(VARCHAR(3),MIN(InDay)) + '~' + CONVERT(VARCHAR(3),MAX(InDay))

    So it would look like 1~4 or 8~8

    Any suggestion? Or additional information you need please let me know

    The use of a CROSS-TAB would be faster than PIVOT and probably just as easy. See the following article...

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

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 21, 2011 at 4:07 pm

    #1289647

    I was a little bored, so I'm going to throw a fish...

    declare @sample table (class Char(1), value int)

    insert into @sample

    select 'A',3 union all

    select 'A',19 union all

    select 'B',8 union all

    select 'A',3 union all

    select 'A',12 union all

    select 'B',1 union all

    select 'B',21 union all

    select 'A',14 union all

    select 'A',6 union all

    select 'B',11 union all

    select 'B',6

    -- cross-tab of result column returned from subquery "dt"

    select max(case when class = 'A' then result else null end) as Class_A

    ,max(case when class = 'B' then result else null end) as Class_B

    from (select class, CAST(min(value) as varchar)+'~'+CAST(max(value) as varchar) as result

    from @sample

    group by class

    )dt

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

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