SQl表横竖转换

zxlshashal

在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


哪位高手请指点。在线等。谢谢

kajaje

通过递归的select变量可以完成表的横向显示。举个简单的例子：
declare @column varchar(1024)
set @column=''
select @column=@column+cardno+''

joe_xuqj

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

kajaje

通过递归的select变量可以完成表的横向显示。举个简单的例子：
declare @column varchar(1024)
set @column=''
select @column=@column+cardno+''

zxlshashala

谢谢楼上两位，我先试试。非常感谢提供思路给我。

joe_xuqj

不好意思，前面的写错了，中间的一部分应该是下面的这个格式。
select cardno,day 'A1',0 'A2',0 'A3',0 'A4',0 'A5',0 'A6',0 'A7' from VIEW where TYPE='A1'

Trellnet

补充一下, 应该就是这样:

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

yunyesyes

　　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

cosio

/*
在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

*/

declare @t table
(
        cardno         int,
        type         char(2),
        Cday        int
)
insert into @t
select 1,'A1',5 union all
select 1,'A2',6 union all
select 2,'A3',7 union all
select 2,'A4',8

select * from @t

select cardno,
       A1=sum(case type when 'A1' then isnull(Cday,0) end),
       A2=sum(case type when 'A2' then isnull(Cday,0) end),
       A3=sum(case type when 'A3' then isnull(Cday,0) end),
       A4=sum(case type when 'A4' then isnull(Cday,0) end),
       A5=sum(case type when 'A5' then isnull(Cday,0) end),
       A6=sum(case type when 'A6' then isnull(Cday,0) end),
       A7=sum(case type when 'A7' then isnull(Cday,0) end)
from @t
group by cardno

zxlshashala

最初由 joe_xuqj 发布
[B]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 [/B]

最上边一行，A1，A2……是字符类型，怎么可以所用 Sum