有如下数据:表名:tsDict
ID Type Money
11 金币销售 -128
11 牛币直充 500
12 牛币直充 152
12 金币销售 -158
12 牛币调整 96
14 金币销售 -192
14 牛币提现 82得出查询结果为:
ID 金币销售 牛币直充 牛币调整 牛币提现
11 -128 500 0 0
12 -158 152 96 0
14 -192 0 0 82type有几种类型,则显示几列
ID Type Money
11 金币销售 -128
11 牛币直充 500
12 牛币直充 152
12 金币销售 -158
12 牛币调整 96
14 金币销售 -192
14 牛币提现 82得出查询结果为:
ID 金币销售 牛币直充 牛币调整 牛币提现
11 -128 500 0 0
12 -158 152 96 0
14 -192 0 0 82type有几种类型,则显示几列
go
set nocount on
if object_id(N'A') is not null drop table A
go
create table A--创建测试数据库
(
ID int,
[Type] nvarchar(100),
[Money] int
)
go
insert into A--插入测试数据
select 11 ,'金币销售', -128 union all
select 11 ,'牛币直充', 500 union all
select 12 ,'牛币直充', 152 union all
select 12 ,'金币销售', -158 union all
select 12 ,'牛币调整', 96 union all
select 14 ,'金币销售', -192 union all
select 14 ,'牛币提现', 82
godeclare @thetype nvarchar(100)
declare @thetype1 nvarchar(100)
declare @sql nvarchar(200)
set @thetype=''
set @thetype1=''
select @thetype=@thetype+','+[Type] from A group by [Type]
select @thetype1=@thetype1+',isnull('+[Type]+',0) as '+[Type] from A group by [Type]
set @thetype=stuff(@thetype,1,1,'')
set @thetype1=stuff(@thetype1,1,1,'')
set @sql='
select ID,'+@thetype1+' from
(select * from A)AA
pivot
(
max([Money])
for [Type] in ('+@thetype+')
) B'
exec sp_executesql @sql
/*
ID 金币销售 牛币调整 牛币提现 牛币直充
----------- ----------- ----------- ----------- -----------
11 -128 0 0 500
12 -158 96 0 152
14 -192 0 82 0
*/
set @sql = 'select ID '
select @sql = @sql + ' , max(case Type when ''' + Type + ''' then Money else 0 end) [' + Type + ']'
from (select distinct Type from tb) as a
set @sql = @sql + ' from tb group by ID'
exec(@sql)
(
ID int,
[Type] nvarchar(100),
[Money] int
)insert into #temp
select 11 ,N'金币销售', -128 union all
select 11 ,N'牛币直充', 500 union all
select 12 ,N'牛币直充', 152 union all
select 12 ,N'金币销售', -158 union all
select 12 ,N'牛币调整', 96 union all
select 14 ,N'金币销售', -192 union all
select 14 ,N'牛币提现', 82SELECT
ID,
金币销售 = SUM(CASE [Type] WHEN N'金币销售' THEN [Money] ELSE 0 END),
牛币直充 = SUM(CASE [Type] WHEN N'牛币直充' THEN [Money] ELSE 0 END),
牛币调整 = SUM(CASE [Type] WHEN N'牛币调整' THEN [Money] ELSE 0 END),
牛币提现 = SUM(CASE [Type] WHEN N'牛币提现' THEN [Money] ELSE 0 END)
FROM #temp
GROUP BY ID