2

I have table_A and want to have a result like table_B. I can accomplish this by using a WHILE loop with a counter a loop 12 times to get my data in the format I want in table_B. Wondering if there was a cleaner way. Thank you!

enter image description here

0
3

--Unpivot the table.

SELECT Location, Account,Dept, Year, Month, Amount 
FROM 
   (SELECT Location, Account,Dept, Year, Month, Amount, January ... Dec
   FROM Table_A) p
UNPIVOT
   (Amount FOR Month IN 
      (January, Feb ... Dec)
)AS Table_B;
GO

reference: link

3

@DecoderReloaded showed how this is typically accomplished using the built-in UNPIVOT method.

This answer shows an alternate method of using CROSS APPLY to extend the functionality that is possible by using UNPIVOT.

Create a table:

USE tempdb;

IF OBJECT_ID('tempdb..#TableA') IS NOT NULL
DROP TABLE #TableA;
CREATE TABLE #TableA
(
    [Location] VARCHAR(30) NOT NULL
    , Account VARCHAR(30) NOT NULL
    , Dept CHAR(2) NOT NULL
    , [Year] INT NOT NULL
    , January DECIMAL(10,2) NOT NULL
    , February DECIMAL(10,2) NOT NULL
    , March DECIMAL(10,2) NOT NULL
    , April DECIMAL(10,2) NOT NULL
    , May DECIMAL(10,2) NOT NULL
    , June DECIMAL(10,2) NOT NULL
    , July DECIMAL(10,2) NOT NULL
    , August DECIMAL(10,2) NOT NULL
    , September DECIMAL(10,2) NOT NULL
    , October DECIMAL(10,2) NOT NULL
    , November DECIMAL(10,2) NOT NULL
    , December DECIMAL(10,2) NOT NULL
);

Insert some data:

INSERT INTO #TableA(Location, Account, Dept, Year, January, February, March, April, May, June, July, August, September, October, November, December)
VALUES ('Store1', '5151', '10', 2015, 110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210, 220)
    , ('Store2', '5151', '10', 2015, 111, 121, 131, 141, 151, 161, 171, 181, 191, 201, 211, 221)
    , ('Store3', '5150', '11', 2015, 112, 122, 132, 142, 152, 162, 172, 182, 192, 202, 212, 222);

"Unpivot" the data using CROSS APPLY:

SELECT a.Location
    , a.Account
    , a.Dept
    , a.Year
    , up.MonthCol
    , up.MonthVal
    , YearlyTotalByLocAcctDept = SUM(up.MonthVal) OVER (PARTITION BY a.Location, a.Account, a.Dept, a.Year)
    , PercentOfTotalByLocAcctDept = CONVERT(DECIMAL(10, 2), CONVERT(DECIMAL(10, 2), up.MonthVal) / SUM(up.MonthVal) OVER (PARTITION BY a.Location, a.Account, a.Dept, a.Year) * 100)
    , YearlyTotal = SUM(up.MonthVal) OVER ()
    , PercentOfTotal = CONVERT(DECIMAL(10, 2), CONVERT(DECIMAL(10, 2), up.MonthVal) / SUM(up.MonthVal) OVER () * 100)
FROM #TableA a
CROSS APPLY (
    VALUES ('January', a.January, 1)
        , (' February', a.February, 2)
        , (' March', a.March, 3)
        , (' April', a.April, 4)
        , (' May', a.May, 5)
        , (' June', a.July, 6)
        , (' July', a.July, 7)
        , (' August', a.August, 8)
        , (' September', a.September, 9)
        , (' October', a.October, 10)
        , (' November', a.November, 11)
        , (' December', a.December, 12)
        ) up(MonthCol, MonthVal, MonthNum)
ORDER BY a.Location
    , a.Account
    , a.Dept
    , a.Year
    , up.MonthNum;

The output from the SELECT query:

enter image description here

As you can see from the output, the single row input data is pivoted into multiple rows, one for each month. I've also added 4 columns to the query that provide further analysis of the data. These 4 extra columns are not easily generated when using the standard UNPIVOT operator.

I wrote a blog post about the use of CROSS APPLY.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.