Need help in Pivot Table with Column Names to Rows.

  • Hi,

    Need help in Pivot function. I have a table with following rows.

    FY REVCODE Jul Jun

    2015 BNQ 1054839 2000000

    2015 FNB 89032 1000000

    2015 RS 1067299 3000000

    I am looking to convert it to

    Month BNQ FNB RS

    JUL 1054839 89032 1067299

    JUN 2000000 1000000 3000000

    I tried with the following and result is coming for one month i.e. JUL but not with the second Month i.e Jun

    SELECT 'Jul1' AS MON, [BNQ], [FNB], [RS]

    FROM

    (SELECT REVENUECODE, SUM(ROUND(((Jul/31)*30),0)) AS JUL

    FROM RM_USERBUDGETTBL

    WHERE USERNAME='rahul' AND FY=2015

    GROUP BY REVENUECODE, USERNAME

    ) AS SourceTable

    PIVOT

    (SUM(JUL) FOR REVENUECODE IN ([BNQ], [FNB], [RS])) AS PivotTable

    Results:

    MONTHBNQ FNB RS

    Jul11054839 89032 1067299

    Thanks in advance..

    -Rahuul

  • This should give you what you're looking for...

    -- Test data --

    IF OBJECT_ID('tempdb..#temp') IS NOT NULL

    DROP TABLE #temp;

    CREATE TABLE #temp (

    FY INT,

    REVCODE VARCHAR(3),

    Jul INT,

    Jun INT

    );

    INSERT #temp (FY,REVCODE,Jul,Jun) VALUES

    (2015,'BNQ',1054839,2000000),

    (2015,'FNB',89032,1000000),

    (2015,'RS',1067299,3000000);

    -- The solution --

    SELECT

    t.FY,

    x.[Month],

    SUM(CASE WHEN t.REVCODE = 'BNQ' THEN x.Amount ELSE 0 END) AS BNQ,

    SUM(CASE WHEN t.REVCODE = 'FNB' THEN x.Amount ELSE 0 END) AS FNB,

    SUM(CASE WHEN t.REVCODE = 'RS' THEN x.Amount ELSE 0 END) AS RS

    FROM

    #temp t

    CROSS APPLY (VALUES ('Jul', t.Jul), ('Jun', t.Jun)) x ([Month],Amount)

    GROUP BY

    t.FY,

    x.[Month]

    The results...

    FYMonthBNQFNBRS

    2015Jul1054839890321067299

    2015Jun200000010000003000000

  • Thanks a lot Jason.

    Saved my day!!

    On another note, could it be done using Pivot?

    Thanks again.

    Rahuul

  • Rahuul (8/2/2015)


    Thanks a lot Jason.

    Saved my day!!

    You're welcome! Glad to help. 🙂

    On another note, could it be done using Pivot?

    Yes. There are several ways to write this query.

  • Rahuul (8/2/2015)


    Thanks a lot Jason.

    Saved my day!!

    On another note, could it be done using Pivot?

    Thanks again.

    Rahuul

    Using PIVOT for this can make things slower but, for more information, please see the following articles.

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

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

    --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 5 posts - 1 through 4 (of 4 total)

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