Aggregate and alias on UNPIVOT

Question

How can I rename (alias) the default States (eg: QLD to Quensland) as an output from the below query? Also how can I aggregate (SUM) the states' forecast to a higher level (eg: National)?

SELECT PRODUCT_BK, Month, State, Forecast
FROM
(SELECT * FROM Forecast_Table) t
UNPIVOT
(Forecast FOR State IN (SA_NT, QLD, VIC_TAS, WA, NSW_ACT)) AS fcst

Thank you

This is a follow up to this question.

Answer 1

you can use case for such purposes

SELECT PRODUCT_BK,
       MONTH,
       CASE [State] WHEN 'QLD' THEN 'Quensland'
                    WHEN 'SA_NT' THEN 'something else'
                    ELSE [State]
       END AS [State],
       Forecast
FROM (SELECT * FROM Forecast_Table) t 
UNPIVOT (Forecast FOR State IN (SA_NT, QLD, VIC_TAS, WA, NSW_ACT)) AS fcst

or, as alternative way, you can create #temp_table or @variable_table or table in your db, fill this table with old & new names and join final output with this table.

example:

-- create variable table to store reference between old and new alias
DECLARE @Sometable AS TABLE
    (
      OldName NVARCHAR(10) ,
      ShouldBeName NVARCHAR(20)
    )
INSERT  @Sometable
        ( OldName, ShouldBeName )
VALUES  ( 'SA_NT', 'Some Name 1' ),
        ( 'QLD', 'Some Name 2' ),
        ( 'VIC_TAS', 'Some Name 3' );

--final query
SELECT  fcst.PRODUCT_BK ,
        fcst.Month ,
        COALESCE(S.ShouldBeName, fcst.[State]) AS [State] ,
        fcst.Forecast
FROM    ( SELECT * FROM Forecast_Table ) t
        UNPIVOT ( Forecast FOR State IN ( SA_NT, QLD, VIC_TAS, WA, NSW_ACT ) ) AS fcst
        LEFT JOIN @Sometable AS S ON fcst.[State] = S.OldName

Or, even like this:

SELECT  fcst.PRODUCT_BK ,
        fcst.Month ,
        COALESCE(S.ShouldBeName, fcst.[State]) AS [State] ,
        fcst.Forecast
FROM    ( SELECT * FROM Forecast_Table ) t 
        UNPIVOT ( Forecast FOR State IN ( SA_NT, QLD, VIC_TAS, WA, NSW_ACT ) ) AS fcst
        LEFT JOIN ( SELECT  *
                    FROM    ( VALUES ( 'SA_NT', 'Some Name 1' ), 
                                     ( 'QLD', 'Some Name 2' ),
                                     ( 'VIC_TAS', 'Some Name 3' ) 
                             ) AS S ( OldName, ShouldBeName ) 
                   ) AS S ON fcst.[State] = S.OldName