表的纵横转化 sqlserver

在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