CODEWITHSUNDEEP
sqlsql-serverunpivot
mcgovec9
mcgovec9

Reputation: 71

SQL server: unpivot column into another and append its value to separate column

ive the following table format(rather its a view), with a sample example

 Name  | MonthYear |      Type     | budget | actual | revenue_forecast
google |   Nov-20  | Gross Billing |   50   |   70   | 40

I want to make it so that i have two rows, with the 'revenue_forecast' becoming a type, and its value to display under budget, like so

 Name  | MonthYear |      Type        | budget | actual 
google |   Nov-20  | Gross Billing    |   50   |   70   
google |   Nov-20  | revenue_forecast |   40   |   null

any ideas how this can be done? struggling a bit with the unpivot logic for this scenario

Upvotes: 0

Views: 370

Answers (1)

Zhorov
Zhorov

Reputation: 29943

You may try to unpivot using VALUES table value constructor, but consider carefully the data types of the columns:

SELECT t.Name, t.MonthYear, v.[Type], v.budget, v.actual
FROM YourTable t
CROSS APPLY (VALUES
   (t.[type], t.budget, t.actual),
   ('revenue_forecast', t.revenue_forecast, NULL)
) v ([type], budget, actual)

The following full query can be used to test this:

declare @table Table
(
    Name varchar(50),
    MonthYear varchar(10),
    Type Varchar(50),
    budget int,
    actual int,
    revenue_forecast int
)
INSERT INTO @table (Name, MonthYear, Type, budget, actual, revenue_forecast)
Values('google', 'Nov-20','Gross Billing',50,70,40)

select * from @table

SELECT t.Name, t.MonthYear, v.[Type], v.budget, v.actual
FROM @table t
CROSS APPLY (VALUES
   (t.[type], t.budget, t.actual),
   ('revenue_forecast', t.revenue_forecast, NULL)
) v ([type], budget, actual)

Upvotes: 4

Related Questions