Post reply

need a query to convert colums into rows

  • December 17, 2015 at 8:11 pm

    #303307

    Hi.. i need to make a query to convert columns into some rows

    Here's the sample table

    CREATE TABLE #UnPivotSampleTable

    (

    [Year] INT, Account VARCHAR(10), CT VARCHAR(10), Budget_1 MONEY, Budget_2 MONEY, Budget_3 MONEY, Budget_4 MONEY, Budget_5 MONEY, Budget_6 MONEY, Budget_7 MONEY, Budget_8 MONEY, Budget_9 MONEY, Budget_10 MONEY, Budget_11 MONEY, Budget_12 MONEY

    )

    GO

    INSERT INTO #UnPivotSampleTable

    SELECT 2015,'10100','A',100,200,300,400,500,600,700,800,900,1000,1100,1200

    UNION ALL

    SELECT 2015,'10200','B',100,200,300,400,500,600,700,800,900,1000,1100,1200

    UNION ALL

    SELECT 2015,'10300','C',100,200,300,400,500,600,700,800,900,1000,1100,1200

    GO

    SELECT * FROM #UnPivotSampleTable

    GO

    i want the result seems like this :

    Year Account CT Month Budget

    2015 10100 A 1 100

    2015 10100 A 2 200

    2015 10100 A 3 300

    2015 10100 A 4 400

    2015 10100 A 5 500

    2015 10100 A 6 600

    2015 10100 A 7 700

    2015 10100 A 8 800

    2015 10100 A 9 900

    2015 10100 A 10 1000

    2015 10100 A 11 1100

    2015 10100 A 12 1200

    Thanks...

  • December 17, 2015 at 8:33 pm

    #1846685

    Here you go. 🙂

    IF OBJECT_ID('tempdb..#UnPivotSampleTable', 'U') IS NOT NULL

    DROP TABLE #UnPivotSampleTable;

    CREATE TABLE #UnPivotSampleTable

    (

    [Year] INT, Account VARCHAR(10), CT VARCHAR(10), Budget_1 MONEY, Budget_2 MONEY, Budget_3 MONEY, Budget_4 MONEY, Budget_5 MONEY, Budget_6 MONEY, Budget_7 MONEY, Budget_8 MONEY, Budget_9 MONEY, Budget_10 MONEY, Budget_11 MONEY, Budget_12 MONEY

    );

    GO

    INSERT INTO #UnPivotSampleTable

    SELECT 2015,'10100','A',100,200,300,400,500,600,700,800,900,1000,1100,1200

    UNION ALL

    SELECT 2015,'10200','B',100,200,300,400,500,600,700,800,900,1000,1100,1200

    UNION ALL

    SELECT 2015,'10300','C',100,200,300,400,500,600,700,800,900,1000,1100,1200;

    GO

    SELECT

    upst.Year,

    upst.Account,

    upst.CT,

    x.MonthNum,

    x.Budget

    FROM

    #UnPivotSampleTable upst

    CROSS APPLY (VALUES (1, upst.Budget_1), (2, upst.Budget_2), (3, upst.Budget_3), (4, upst.Budget_4), (5, upst.Budget_5), (6, upst.Budget_6),

    (7, upst.Budget_7), (8, upst.Budget_8), (9, upst.Budget_9), (10, upst.Budget_10), (11, upst.Budget_11), (12, upst.Budget_12)

    ) x (MonthNum, Budget)

    ;

  • December 17, 2015 at 8:50 pm

    #1846686

    thanks for your great answer...!

  • December 17, 2015 at 10:10 pm

    #1846693

    No problem. Glad to help.

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

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