What are the T-SQL codes to achieve this specific output from an existing View Table?

Question

I am running SQL Server 2016 and I have the following simple T-SQL query which gives the following output:

Query:

SELECT * from ExtrasViewTable

Outputs:

 ID      Property     F&B    SPA    TotalExp        Mth
  1        ABC        100     0       100        2014-09-01
  2        XYZ         50    20        70        2014-10-01

I want to change the output to the following:

 ID     Property        Mth          ExpCat       ExpCatAmt
  1       ABC        2014-09-01       F&B            100
  1       ABC        2014-09-01       SPA              0
  1       ABC        2014-09-01       TotalExp       100
  2       XYZ        2014-10-01       F&B             50
  2       XYZ        2014-10-01       SPA             20
  2       XYZ        2014-10-01       TotalExp        70

I am having a hard time finding the correct T-SQL codes to achieve this. I have tried a few basic Pivot operations but they were wrong.

ExpCat and ExpCatAmt are user-defined names. – user3115933 Sep 14 '16 at 12:53 — user3115933, Sep 14 '16 at 12:53
This is an UNPIVOT. Google that term for examples. – Tab Alleman Sep 14 '16 at 13:02 — Tab Alleman, Sep 14 '16 at 13:02

score 0 · Accepted Answer · answered Sep 14 '16 at 13:22

You can use UnPivot,

DECLARE @ExtrasViewTable TABLE (
    ID INT
    ,Property VARCHAR(10)
    ,[F&B] INT
    ,SPA INT
    ,TotalExp INT
    ,Mth DATE
    )

insert into @ExtrasViewTable values 
(1,'ABC',100, 0,100,'2014-09-01')
,(2,'XYZ', 50,20, 70,'2014-10-01')

SELECT ID,Property,Mth,Expcat,Expcatamt
FROM @ExtrasViewTable
unpivot(Expcatamt FOR Expcat IN (
            [F&B]
            ,SPA
            ,TotalExp
            )) unpiv;

What are the T-SQL codes to achieve this specific output from an existing View Table?

1 Answers1