Post reply

Pivot Query

  • November 16, 2012 at 12:49 am

    #270311

    Hi all,

    I have a table with two columns, Key & Value

    Key | Value

    ------------

    Key1 | Value1

    Key2 | Value2

    Key3 | value3

    Now, I want to somehow pivot these table, such that the result is a single row with Key(s) as the column name

    Key1 | Key2 | Key3

    ------------------------

    Value1 | Value2 | Value3

    I have seed Pivoting of SQL Server, but it needs aggregate function. But these case do not need any aggregate. SO STUCKED!!

    Thanks,

    K

  • November 16, 2012 at 12:58 am

    #1559658

    It might be worth you reading these excelent article by Jeff Moden on Cross Tabs and pivots.

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

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

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • November 16, 2012 at 1:00 am

    #1559660

    try this if you know columns(limited columns)

    select

    (case when key='Key1' then value) as 'Key 1'

    ,(case when key='Key2' then value) as 'Key 2'

    ,(case when key='Key3' then value) as 'Key 3'

    from tableA

    if to many key values then go for dynamic pivot ,dynamic row to column queries.

  • November 16, 2012 at 2:16 am

    #1559689

    Jason : I read those articles. They are excellent!! But afterall, they use aggregate functions and that is not what I want.

    BriPan : I tried your query, but still something is missing here. Say, if I have two rows, I got the following result:

    Key1 | Key2

    --------------

    Value1 | NULL

    NULL | Value2

    Basically, I want the output in a single row, instead of 2 or more

  • November 16, 2012 at 2:20 am

    #1559691

    kunal.desai 7690 (11/16/2012)

    Jason : I read those articles. They are excellent!! But afterall, they use aggregate functions and that is not what I want.

    BriPan : I tried your query, but still something is missing here. Say, if I have two rows, I got the following result:

    Key1 | Key2

    --------------

    Value1 | NULL

    NULL | Value2

    Basically, I want the output in a single row, instead of 2 or more

    thats why you'd use a MAX to eliminate the nulls, as per a Cross Tab

    select

    Max(case when key='Key1' then value) as 'Key 1'

    ,Max(case when key='Key2' then value) as 'Key 2'

    ,Max(case when key='Key3' then value) as 'Key 3'

    from tableA

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • November 16, 2012 at 2:43 am

    #1559696

    BANG ON!!

    That's what I was looking out for! Thanks mate

    -K

  • November 16, 2012 at 3:51 am

    #1559716

    No problem, and technically MAX is classed as an aggregation function in BoL,

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • November 16, 2012 at 4:11 pm

    #1559956

    BriPan (11/16/2012)

    try this if you know columns(limited columns)

    select

    (case when key='Key1' then value) as 'Key 1'

    ,(case when key='Key2' then value) as 'Key 2'

    ,(case when key='Key3' then value) as 'Key 3'

    from tableA

    if to many key values then go for dynamic pivot ,dynamic row to column queries.

    The will give you a "herring bone" output without and aggregate like MAX on the CASE and a GROUP BY on the query.

    --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)

Viewing 8 posts - 1 through 7 (of 7 total)

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