CODEWITHSUNDEEP
sql-server
Gradiyanto Sanjaya
Gradiyanto Sanjaya

Reputation: 115

How to unpivot a column with different data type SQL Server

I have a query like this

SELECT 
    totalvolume = SUM(volume),
    totalusage = SUM(usage),
    percentage = CAST(SUM(usage) * 100 / SUM(volume) as decimal (10,2)),   
    YEAR 
FROM 
    table1

and I get table like below

|totalvolume|totalusage|percentage|Year
|--------------------------------------
| 100       | 50       | 50.00    |2016  
| 200       | 50       | 25.00    |2015

My goal is to make the list table goes like this using PIVOT:

|Type        |2015  |2016   |
|------------------------   |
| totalvolume| 200  | 100   |
| totalusage | 50   | 50    |
| percentage |25.00|50.00 |

So I decide to create this table first using a) UNPIVOT:

Type        | Value | year
-------------------
totalvolume |100    |2016
totalusage  |50     |2016
percentage  |50.00  |2016 
totalvolume |200    |2015
totalusage  |50     |2015
percentage  |25.00  |2015

but in my UNPIVOT query

SELECT 
    Type, Value, Year
FROM 
    <my table query>
UNPIVOT 
    (value FOR Type IN (totalvolume, totalusage, percentage))

There's an error in my SQL:

The type of column "percentage" conflicts with the type of other columns specified in the UNPIVOT list

So is there any solution to solve this problem without changing the data type?

If I exclude the percentage column I could get the view I want using pivot so it be like this

|Type        |2015  |2016   |
|------------------------   |
| totalvolume| 200  | 100   |
| totalusage | 50   | 50    |

But I need the percentage row, too.

Thanks in advance

Upvotes: 4

Views: 13686

Answers (3)

user3315260
user3315260

Reputation: 1

Using the system tables you could do something like this. if you wanted to add a where clause or something else you could do it in the dynamic sql.


Declare @TableName nvarchar(50), @PrimaryKey nvarchar(50)
Select @TableName = 'TableName', @PrimaryKey = 'PrimaryKey(or unpivot value)'

Declare @JsonConvertColumns nvarchar(max), @UnpivotColumns nvarchar(max)
Declare @SQLQuery nvarchar(max)

select @JsonConvertColumns = Coalesce(@JsonConvertColumns + ',','') + ' string_escape(cast('+columns.name+' as nvarchar(max)), ''JSON'') as ' + columns.name + '' from sys.columns 
    left join sys.tables on tables.object_id = columns.object_id 
     where tables.name = @TableName and columns.name <> @PrimaryKey


select @UnpivotColumns = Coalesce(@UnpivotColumns + ',','') + QuoteName(columns.name) from sys.columns 
    left join sys.tables on tables.object_id = columns.object_id 
     where tables.name = @TableName and columns.name <> @PrimaryKey

Set @SQLQuery = N'SELECT ' + @PrimaryKey + ', FieldName, FieldValue 
FROM (select ' + @PrimaryKey + ', ' + @JsonConvertColumns + ' from ' + @TableName + ') a UNPIVOT
(
       FieldValue
       FOR FieldName IN (' + @UnpivotColumns + ')
       ) as p'

Exec sp_executesql @SQLQuery

Upvotes: 0

shawnt00
shawnt00

Reputation: 17925

In this case you can convert to a common numeric format for the pivot operation and then format the options for final output.

with data(totalvolume, totalusage, percentage, Year) as (
    select 100, 50, 50.00, 2016 union all
    select 200, 50, 25.00, 2015
), converted(totalvolume, totalusage, percentage, Year) as (
    select
        cast(totalvolume as decimal(8, 2)),
        cast(totalusage as decimal(8, 2)),
        cast(percentage as decimal(8, 2)), 
        cast(Year as decimal(8, 2))
    from data
)   
select
    Type,
    case
        when Type in ('totalvolume', 'totalusage') then format(Value, '0')
        when Type = 'percentage'  then format(Value, '0.00\%')
    end as Value,
    format(Year, '0') as Year
from converted unpivot
    (value for Type int (totalvolume, totalusage, percentage)) as unpvt
order by
    Year, Type desc;

Upvotes: 1

Anton
Anton

Reputation: 2882

There is no way to have different data types within one column. But you can simulate it by converting all the values to varchar like below

SELECT 
    Type, Value, Year
FROM 
    (select 
         convert(varchar(100), totalvolume) as totalvolume,
         convert(varchar(100), totalusage) as totalusage,
         convert(varchar(100), percentage) as totalusage
      from <my table query>) pv
UNPIVOT 
    (value FOR Type IN (totalvolume, totalusage, percentage)) unp

Then, if you need to do some calculations, you will have to do reversal conversions.

Upvotes: 6

Related Questions