行列转换问题

[liang_z]

表T1
编号 类型 数量
1 A 10
1 B 12
1 C 14
2 A 11
2 C 13
....

要用一个Sql语句将它化为
编号 A B C
1 10 12 14
2 11 0 13
....

[firetoucher]

select 编号,aa.A,bb.B,cc.C
from
( select 编号, A = sum(类型)
from T1
where 类型 = 'A'
group by 编号) as aa
( select 编号, B = sum(类型)
from T1
where 类型 = 'B'
group by 编号) as bb
( select 编号, C = sum(类型)
from T1
where 类型 = 'C'
group by 编号) as bb
where aa.编号 *=bb.编号 and aa.编号 *= cc.编号

[liang_z]

呵呵，有点复杂啊，要是类型的个数不定的话，是不是就不能做了呢？

[liang_z]

再顶一下

[tw_cshn]

你可以首先用一条语句 select distinct 类型 from 表T1
得到类型的个数，然后在动态的创建你的SQL我就这样做过

[mrfanghansheng]

firetoucher(风焱)的显然是错的

[tw_cshn]

语句应该这样
表A
create table A
(
ID NUMBER,
LX VARCHAR2(1),
C NUMBER
)
ID A B C

1 1 A 10
2 1 B 12
3 1 C 14
4 2 A 11
5 2 C 13

select b.id,(select sum(c) from a where id=b.id and lx='A') A ,
(select sum(c) from a where id=b.id and lx='B') B,
(select sum(c) from a where id=b.id and lx='C') C
from a b

[pingshx]

上面的老大，最后一行‘*’是不是代表左连接？？

[mrfanghansheng]

select 编号,sum(a) as a,sum(b) as b,sum(c) as c from
(
select 编号,数量 as a,0 as b,0 as c from T1 where 类型 = 'A'

union

select 编号,0 as a,数量 as b,0 as c from T1 where 类型 = 'b'

union

select 编号,0 as a,0 as b,数量 as c from T1 where 类型 = 'c'
) aa group by 编号

[mrfanghansheng]

sqlserver7测试通过

[liang_z]

呵呵，算了，太麻烦了，我还用别的方法吧，谢谢各位了！结贴了

[tw_cshn]

程序这样
var
s,c:string;
begin
s:='select b.id,'
c:='(select sum(c) from a where id=b.id and lx=''%s'') %s ,'
Query1.Close;
Query1.Sql.Text:='select distinct lx from a';
Query1.Open;
while not Query1.Eof do
begin
s:=s+Format(c,[Query1.FieldByName('lx').AsString,
Query1.FieldByName('lx').AsString]);
Query1.Next;
end;
s:=copy(s,1,Length(s)-1);
s:=s+ ' from a b';
这样S最后得到的就是你要的SQL

[tw_cshn]

我在ORACLE里测试通过

[tw_cshn]

哦对了最好还要加个GROUP BY去掉重复的数据

select cc.id,cc.a,cc.b,cc.c from
(select b.id,(select sum(c) from a where id=b.id and lx='A') A ,
(select sum(c) from a where id=b.id and lx='B') B,
(select sum(c) from a where id=b.id and lx='C') C
from a b)cc
group by cc.id,cc.a,cc.b,cc.c

[firetoucher]

如果你用sql server 2000，利用函数会简单点。
（不好意思，上面的少了','，而且先应该union一次：）

[liang_z]

已经提交了FAQ，
请： firetoucher(风焱) 兄弟讲一下关于MS SQL 2K中用函数解决的方法，
我可以另开一贴

[火龙岛主]

sql server的答案：

select 编号,
(case 类型 when a then 数量 end ) a,
(case 类型 when b then 数量 end ) b,
(case 类型 when c then 数量 end ) c
from T1

[「已注销」]

标记一下吧：该类问题属于数据透视