在sqlserver中,我有view结果如下:
cardno type day
001 A1 5
001 A2 4
002 A1 3
002 A3 6
……
其中type总共有A1,A2,A3 ……A7七个值,我现在要变成如下表table1:
cardno A1 A2 A3 A4 A5 A6 A7
001 5 4 0 0 0 0 0
002 3 0 6 0 0 0 0
通过递归的select变量可以完成表的横向显示。举个简单的例子:
declare @column varchar(1024)
set @column=''
select @column=@column+cardno+''
select cardno, sum(A1) 'A1',sum(A2) 'A2', sum(A3) 'A3',sum(A4) 'A4', sum(A5) 'A5',sum(A6) 'A6', sum(A7) 'A7'
from
(select cardno,A1 'A1',0 'A2',0 'A3',0 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A1'
union all
select cardno,0 'A1',A2 'A2',0 'A3',0 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A2'
union all
select cardno,0 'A1',0 'A2',A3 'A3',0 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A3'
union all
select cardno,0 'A1',0 'A2',0 'A3',A4 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A4'
union all
select cardno,0 'A1',0 'A2',0 'A3',0 'A4',A5 'A5',0 'A6',0 'A7' from VIEW where TYPE='A5'
union all
select cardno,0 'A1',0 'A2',0 'A3',0 'A4',0 'A5',A6 'A6',0 'A7' from VIEW where TYPE='A6'
union all
select cardno,0 'A1',0 'A2',0 'A3',0 'A4',0 'A5',0 'A6',A7 'A7' from VIEW where TYPE='A7'
)a
group by cardno
select cardno, sum(A1) 'A1',sum(A2) 'A2', sum(A3) 'A3',sum(A4) 'A4', sum(A5) 'A5',sum(A6) 'A6', sum(A7) 'A7'
from (
select cardno,day 'A1',0 'A2',0 'A3',0 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A1'
union all
select cardno,0 'A1',day 'A2 ',0 'A3',0 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A2'
union all
select cardno,0 'A1',0 'A2 ',day 'A3',0 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A3'
union all
select cardno,0 'A1',0 'A2',0 'A3',day 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A4'
union all
select cardno,0 'A1',0 'A2',0 'A3',0 'A4',day 'A5',0 'A6',0 'A7' from VIEW where TYPE='A5'
union all
select cardno,0 'A1',0 'A2',0 'A3',0 'A4',0 'A5',day 'A6',0 'A7' from VIEW where TYPE='A6'
union all
select cardno,0 'A1',0 'A2',0 'A3',0 'A4',0 'A5',0 'A6',day 'A7' from VIEW where TYPE='A7'
)a group by cardno
declare @sql varchar(8000)
set @sql = 'select cardno,'
select @sql = @sql + '(case type when '''+type +'''
then day else 0 end) as '''+type +''','
from (select distinct type from view) as a
select @sql = left(@sql,len(@sql)-1) + ' from view group by cardno'
exec(@sql)
go
cardno type day
001 A1 5
001 A2 4
002 A1 3
002 A3 6
……
其中type总共有A1,A2,A3 ……A7七个值,我现在要变成如下表table1:
cardno A1 A2 A3 A4 A5 A6 A7
001 5 4 0 0 0 0 0
002 3 0 6 0 0 0 0
通过递归的select变量可以完成表的横向显示。举个简单的例子:
declare @column varchar(1024)
set @column=''
select @column=@column+cardno+''
select cardno, sum(A1) 'A1',sum(A2) 'A2', sum(A3) 'A3',sum(A4) 'A4', sum(A5) 'A5',sum(A6) 'A6', sum(A7) 'A7'
from
(select cardno,A1 'A1',0 'A2',0 'A3',0 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A1'
union all
select cardno,0 'A1',A2 'A2',0 'A3',0 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A2'
union all
select cardno,0 'A1',0 'A2',A3 'A3',0 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A3'
union all
select cardno,0 'A1',0 'A2',0 'A3',A4 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A4'
union all
select cardno,0 'A1',0 'A2',0 'A3',0 'A4',A5 'A5',0 'A6',0 'A7' from VIEW where TYPE='A5'
union all
select cardno,0 'A1',0 'A2',0 'A3',0 'A4',0 'A5',A6 'A6',0 'A7' from VIEW where TYPE='A6'
union all
select cardno,0 'A1',0 'A2',0 'A3',0 'A4',0 'A5',0 'A6',A7 'A7' from VIEW where TYPE='A7'
)a
group by cardno
select cardno, sum(A1) 'A1',sum(A2) 'A2', sum(A3) 'A3',sum(A4) 'A4', sum(A5) 'A5',sum(A6) 'A6', sum(A7) 'A7'
from (
select cardno,day 'A1',0 'A2',0 'A3',0 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A1'
union all
select cardno,0 'A1',day 'A2 ',0 'A3',0 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A2'
union all
select cardno,0 'A1',0 'A2 ',day 'A3',0 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A3'
union all
select cardno,0 'A1',0 'A2',0 'A3',day 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A4'
union all
select cardno,0 'A1',0 'A2',0 'A3',0 'A4',day 'A5',0 'A6',0 'A7' from VIEW where TYPE='A5'
union all
select cardno,0 'A1',0 'A2',0 'A3',0 'A4',0 'A5',day 'A6',0 'A7' from VIEW where TYPE='A6'
union all
select cardno,0 'A1',0 'A2',0 'A3',0 'A4',0 'A5',0 'A6',day 'A7' from VIEW where TYPE='A7'
)a group by cardno
declare @sql varchar(8000)
set @sql = 'select cardno,'
select @sql = @sql + '(case type when '''+type +'''
then day else 0 end) as '''+type +''','
from (select distinct type from view) as a
select @sql = left(@sql,len(@sql)-1) + ' from view group by cardno'
exec(@sql)
go