SQL Server 2012中的动态数据透视问题 [英] dynamic pivot issue in sql server 2012

问题描述

我正在尝试在SQL中实现动态透视，以表示下面给出的结果

I'm trying to implement a dynamic pivoting in SQL to represent my results given below

ID  Charge Message  Amt Of Billing 
4563    WEB FEE     9.75
4563    MONTHLY FEE 6
4563    CLUB FEE    9.95
4648    MONTHLY FEE 6
4648    ACCOUNT FEE 5
4648    CLUB FEE    9.95
4648    WEB FEE     9.75
4650    MONTHLY FEE 6
4650    WEB FEE     9.75
4650    CLUB FEE    9.95

转换成所需的表示形式.

into a desired representation like this.

ID     ACCOUNT FEE  MONTHLY FEE CLUB FEE WEB FEE
4563                6           9.95     9.75
4648    5           6           9.95     9.75
4650                6           9.95     9.75

我们非常感谢您的帮助.

Your help is highly appreciated.

推荐答案

对于动态数据透视，您可以使用类似以下的方法:

For a dynamic pivot, you could use something like this:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
  select @cols = stuff((
    select distinct 
      ', ' + quotename(isnull(nullif(ChargeMessage,''),'unknown'))
      from t 
      order by 1
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,1,'')
select  @sql ='
 select Id, ' + @cols +'
  from  (
    select Id, ChargeMessage= isnull(nullif(ChargeMessage,''''),''unknown''), AmtOfBilling
      from t
    ) as t
 pivot (sum([AmtOfBilling]) for [ChargeMessage] in (' + @cols +')) p'
select @sql
exec(@sql);

rextester演示: http://rextester.com/NRRGA52425

rextester demo: http://rextester.com/NRRGA52425

返回:(包括用于测试数据的空字符串)

returns: (included an empty string for test data)

+------+-------------+----------+-------------+---------+---------+
|  Id  | ACCOUNT FEE | CLUB FEE | MONTHLY FEE | unknown | WEB FEE |
+------+-------------+----------+-------------+---------+---------+
| 4563 | NULL        | 9,95     | 6,00        | NULL    | 9,75    |
| 4648 | 5,00        | 9,95     | 6,00        | NULL    | 9,75    |
| 4650 | NULL        | 9,95     | 6,00        | 9,95    | 9,75    |
+------+-------------+----------+-------------+---------+---------+

生成的查询:

select Id,  [ACCOUNT FEE], [CLUB FEE], [MONTHLY FEE], [unknown], [WEB FEE]
  from  (
   select Id, ChargeMessage= isnull(nullif(ChargeMessage,''),'unknown'), AmtOfBilling
     from t
) as t
pivot (sum([AmtOfBilling]) for [ChargeMessage] 
  in ( [ACCOUNT FEE], [CLUB FEE], [MONTHLY FEE], [unknown], [WEB FEE])) p

---

动态条件聚合:

---

dynamic conditional aggregation:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
select @cols = stuff((
    select distinct 
      char(10)+'      , ' 
      + quotename(isnull(nullif(ChargeMessage,''),'unknown'))
      +' = sum(case when ChargeMessage = '''+ChargeMessage+''' then AmtOfBilling end)'
    from t 
    order by 1
    for xml path (''), type).value('.','nvarchar(max)')
  ,1,0,'')
select  @sql ='
select Id'+@cols+'
from t
group by Id'
select @sql
exec(@sql);

查询已生成:

select Id
  , [ACCOUNT FEE] = sum(case when ChargeMessage = 'ACCOUNT FEE' then AmtOfBilling end)
  , [CLUB FEE] = sum(case when ChargeMessage = 'CLUB FEE' then AmtOfBilling end)
  , [MONTHLY FEE] = sum(case when ChargeMessage = 'MONTHLY FEE' then AmtOfBilling end)
  , [unknown] = sum(case when ChargeMessage = '' then AmtOfBilling end)
  , [WEB FEE] = sum(case when ChargeMessage = 'WEB FEE' then AmtOfBilling end)
from t
group by Id

这篇关于SQL Server 2012中的动态数据透视问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！