none
how to see query results in pivot table like structure (from A,B,C column how to make Column A as header) RRS feed

  • Question

  • Hello All,

    please help me with this query result

    select

    lclog.stVal,

    (CASE kt1.mcode WHEN 'I' THEN 'Interactive' WHEN 'M' THEN 'Magazine' WHEN 'N' THEN 'Newspaper'  WHEN 'NET' THEN 'Network'  WHEN 'O' THEN 'Outdoor'  WHEN 'R' THEN 'Radio'  WHEN 'S' THEN 'Supplement'  WHEN 'T' THEN 'Trade'  WHEN 'TV' THEN 'TV & Local Cable'  WHEN 'X' THEN 'Network Radio' ELSE 'UNDEFINE' END) as Mcode

    ,CASE Count(*) WHEN 1 THEN 0
              ELSE count(*) END as TotalDocs

    from lclog left join itms  on  lclog.statenum = itms.statenum 
    left join idat i  on itms.itemnum=i.itemnum left join xItem kx1 on i.itemnum=kx1.itemnum
    left join kTab kt1 on kx1.keywordnum=kt1.keywordnum
    where  flags=12 and scope = 102 and  lclog.statenum not in (101,102)  and i.status <> 16
    group by  lclog.stVal,kt1.mcode
    order by 1,2,3


    Stname,MCode,TotalDocs
    Precription , Trade, 3
    Precription , Magazine,0
    Precription , Newspaper,0
    Precription , Radio, 5
    Precription ,Supplement,8
    Precription ,Network, Radio, 2
    Precription ,Radio, 119
    Precription ,TV & Local Cable, 53
    StandardQue ,Network, 57
    StandardQue ,Interactive,511
    StandardQue ,Magazine, 31
    StandardQue ,Newspaper,17
    StandardQue ,Outdoor,13
    StandardQue ,Supplement,7
    StandardQue ,Trade,13
    StandardQue ,Interactive ,0
    StandardQue ,Network Radio, 0
    StandardQue ,Newspaper, 0
    StandardQue ,Radio, 11
    StandardQue ,Supplement, 20
    StandardQue ,TV & Local Cable, 28
    StandardQue ,Network Radio, 48
    StandardQue ,Radio, 722
    StandardQue ,TV & Local Cable, 363

     

    how can i convery the above results as below (first column values become header like a pivot table)

    is it possible? in sql server

    please sir kindly help me sir,

     

                          Prescription   StandardQue     
    Trade                            3          10
    Magazine                      40         35
    Newspaper                   00
    Radio                              1       300 
    Supplement                  40         59
    Network                                   554   
    TV& Local Cable            43        23
    interactive ...   ...  
    outdoor
    .....
    ........


    Thanks in a ton advance
    asita

    Thursday, July 23, 2009 9:13 PM

Answers

  • Two methods, the second for SQL Server 2005/2008 only:

    SELECT mcode, 
           SUM(CASE WHEN stname = 'Prescription' THEN TotalDocs ELSE 0 END) AS Prescription,
           SUM(CASE WHEN stname = 'StandardQue' THEN TotalDocs ELSE 0 END) AS StandardQue
    FROM (
    <your current query here without ORDER BY>
    ) AS T
    GROUP BY mcode;
    


    SELECT mcode, Prescription, StandardQue FROM ( <your current query here without ORDER BY> ) AS T PIVOT (SUM(TotalDocs) FOR stname IN (Prescription, StandardQue)) AS P;

    Plamen Ratchev
    Thursday, July 23, 2009 9:27 PM
    Moderator
  • You are double pivoting here, and once you use the PIVOT operator the original columns cannot be used. Seems you do not need to use PIVOT at all, try this:

    <there was a query here>

    Plamen Ratchev
    • Marked as answer by asitti7 Friday, July 24, 2009 8:56 PM
    • Edited by Plamen RatchevModerator Friday, July 24, 2009 9:49 PM OP requested to remove query for security reasons
    Friday, July 24, 2009 8:41 PM
    Moderator

All replies

  • Two methods, the second for SQL Server 2005/2008 only:

    SELECT mcode, 
           SUM(CASE WHEN stname = 'Prescription' THEN TotalDocs ELSE 0 END) AS Prescription,
           SUM(CASE WHEN stname = 'StandardQue' THEN TotalDocs ELSE 0 END) AS StandardQue
    FROM (
    <your current query here without ORDER BY>
    ) AS T
    GROUP BY mcode;
    


    SELECT mcode, Prescription, StandardQue FROM ( <your current query here without ORDER BY> ) AS T PIVOT (SUM(TotalDocs) FOR stname IN (Prescription, StandardQue)) AS P;

    Plamen Ratchev
    Thursday, July 23, 2009 9:27 PM
    Moderator
  • Thanks Palmen

    second one was worked me very well

    Thank you very much


    you might forgot end in case statement, while try to give me quick response
    this may help some others


    Palmen i really grateful to you

    Thaks Lord

    asita
    Thursday, July 23, 2009 10:48 PM

  • i have a quick question

    is there any wat to show total at the bottom of results,


    so it will be example

                      Prescription            StandardQue     
    Trade                            3          10
    Magazine                      40         35
    Newspaper                   00
    Radio                            1          300 
    TOTAL                      44              345


    please help me

    thanks in advance
    asita

    Thursday, July 23, 2009 10:52 PM
  • Please help me to find out

    is it possible?


    please/......
    Thursday, July 23, 2009 11:56 PM
  • Yes, I just noted I forgot the END for the CASE expressions and made an edit to add them.
    Plamen Ratchev
    Friday, July 24, 2009 2:08 AM
    Moderator
  • Here is one method to add the totals:

    SELECT CASE WHEN GROUPING(mcode) = 1
                THEN 'Total'
                ELSE mcode
           END AS mcode, SUM(Precription) AS precription, SUM(StandardQue) AS standardque
    FROM (
    <your current query here without ORDER BY>
    ) AS T
    PIVOT
    (SUM(TotalDocs) FOR stname IN (Precription, StandardQue)) AS P
    GROUP BY mcode
    WITH ROLLUP
    ORDER BY GROUPING(mcode), mcode;

    Plamen Ratchev
    Friday, July 24, 2009 2:16 AM
    Moderator
  • Hi Plamen


    i tried as below it shows be the below errors could you please check

    and also you extended query from second model (from your first post)

    is it possible to extend with first model

    could you please help me , i am in the final stage of my problem

    Please sir

    Thanks in advance
    asita

    ===============
    some query (due to security reasons removed sorry)
    ========

    RESULT

    Msg 207, Level 16, State 1, Line 5
    Invalid column name 'Stat'.
    Msg 207, Level 16, State 1, Line 5
    Invalid column name 'Stat'.
    Msg 207, Level 16, State 1, Line 5
    Invalid column name 'Stat'.
    Msg 207, Level 16, State 1, Line 5
    Invalid column name 'Stat'.
    Msg 207, Level 16, State 1, Line 5
    Invalid column name 'TotalDocs'.
    Msg 207, Level 16, State 1, Line 6
    Invalid column name 'Stat'.
    Msg 207, Level 16, State 1, Line 6
    Invalid column name 'Stat'.
    Msg 207, Level 16, State 1, Line 6
    Invalid column name 'Stat'.
    Msg 207, Level 16, State 1, Line 6
    Invalid column name 'Stat'.
    Msg 207, Level 16, State 1, Line 6
    Invalid column name 'TotalDocs'.
    Msg 207, Level 16, State 1, Line 7
    Invalid column name 'Stat'.
    Msg 207, Level 16, State 1, Line 7
    Invalid column name 'TotalDocs'.
    Msg 207, Level 16, State 1, Line 8
    Invalid column name 'Stat'.
    Msg 207, Level 16, State 1, Line 8
    Invalid column name 'TotalDocs'.
    Msg 207, Level 16, State 1, Line 9
    Invalid column name 'Stat'.
    Msg 207, Level 16, State 1, Line 9
    Invalid column name 'TotalDocs'.

    • Edited by asitti7 Friday, July 24, 2009 8:56 PM
    Friday, July 24, 2009 6:49 PM
  • Does PIVOT goes inside the inner loop? rather than outside loop?


    please....

    Friday, July 24, 2009 7:35 PM
  • any ideas please sir???????????
    Friday, July 24, 2009 8:15 PM
  • You are double pivoting here, and once you use the PIVOT operator the original columns cannot be used. Seems you do not need to use PIVOT at all, try this:

    <there was a query here>

    Plamen Ratchev
    • Marked as answer by asitti7 Friday, July 24, 2009 8:56 PM
    • Edited by Plamen RatchevModerator Friday, July 24, 2009 9:49 PM OP requested to remove query for security reasons
    Friday, July 24, 2009 8:41 PM
    Moderator
  • Oh My God

    it is working as i expected,

    thanks palmen wonder full excellenet

    I GRATE FULL TO YOU

    Thanks very much

    i dont have any words ..., its totally blank my mind

    but one thing could you plese remove the just query due to security reasons please(just in your previous post).., please
    Friday, July 24, 2009 8:55 PM
  • I removed the query, let me know if that was not the correct one.
    Plamen Ratchev
    Friday, July 24, 2009 9:51 PM
    Moderator
  • yes that is the one Plamen


    once again thank you very much
    Friday, July 24, 2009 9:53 PM