如何在SQL Server中将具有不同数据类型的列转换为行

Question

如何在SQL Server中将具有不同数据类型的列转换为行

4

我有一个类似这样的查询

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

我得到了如下表格。

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

我的目标是使用PIVOT将列表表格变成这样：

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

所以我决定首先使用 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

但在我的UNPIVOT查询中

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

我的SQL中出现了一个错误:

“百分比”列的类型与UNPIVOT列表中指定的其他列的类型冲突

那么是否有解决该问题而不更改数据类型的解决方案呢？

如果我排除百分比列，我可以使用pivot得到所需的视图，代码如下：

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

但我也需要百分比行。

提前感谢。

- Gradiyanto Sanjaya

旋转和格式化（即“百分比”）都是报告工具函数，而不是数据库函数。您用这些数据做什么？将其作为报告发送吗？使用报告工具。 - Nick.McDermaid

3个回答

1

在这种情况下，您可以将数据转换为通用数字格式进行透视操作，并为最终输出格式化选项。

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;

- shawnt00

0

使用系统表，您可以像这样做。如果您想添加where子句或其他内容，可以在动态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

- user3315260

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Anton · Accepted Answer

一个列中无法存在不同的数据类型。但是可以通过将所有值转换为varchar来模拟它，如下所示：

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

那么，如果你需要进行一些计算，你就必须进行反向转换。