27,579
社区成员
发帖
与我相关
我的任务
分享
if not object_id(N'Tempdb..#T') is null drop table #T
Create table #T([PaySerID] int,[PayType] nvarchar(25),[Money] int)
Insert #T
select 1,N'现金',10 union ALL
select 1,N'现金',20 union all
select 1,N'刷卡',2555 union all
select 1,N'支付宝',300 union all
select 1,N'微信',1000 union all
select 2,N'花呗',5 union all
select 2,N'团会员支出',6 union all
select 2,N'积分',778 union all
select 2,N'团购',890 union all
select 2,N'测试',900 union all
select 2,N'转账',1012 union all
select 3,N'团会员支出',110 union all
select 3,N'积分',120 union all
select 3,N'团购',130 union all
select 3,N'测试',14 union all
select 3,N'转账',15
GO
SELECT * FROM (
SELECT [PayType],[money] FROM #T
) AS t
PIVOT(SUM(Money) FOR PayType IN ([支付宝],[微信],[花呗],[团会员支出],[现金],[积分],[团购],[刷卡],[测试],[转账])) p
+-----+------+----+-------+----+-----+------+------+-----+------+
| 支付宝 | 微信 | 花呗 | 团会员支出 | 现金 | 积分 | 团购 | 刷卡 | 测试 | 转账 |
+-----+------+----+-------+----+-----+------+------+-----+------+
| 300 | 1000 | 5 | 116 | 30 | 898 | 1020 | 2555 | 914 | 1027 |
+-----+------+----+-------+----+-----+------+------+-----+------+
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([PaySerID] int,[PayType] nvarchar(25),[Money] int)
Insert #T
select 1,N'现金',1 union all
select 1,N'刷卡',2 union all
select 1,N'支付宝',3 union all
select 1,N'微信4',null union all
select 2,N'花呗',5 union all
select 2,N'团会员支出',6 union all
select 2,N'积分',7 union all
select 2,N'团购',8 union all
select 2,N'测试',9 union all
select 2,N'转账',10 union all
select 3,N'团会员支出',11 union all
select 3,N'积分',12 union all
select 3,N'团购',13 union all
select 3,N'测试',14 union all
select 3,N'转账',15
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select PaySerID'
select @sql=@sql+' , max(case PayType when '''+ [PayType] +''' then Money else 0 end) ['+ [PayType] +']'
from (select distinct [PayType] from #T) as a
set @sql=@sql+' from #T group by PaySerID'
exec(@sql)