如何实行行列转换

[cai21cn]

有表如下：
　rq a b c d
2001-1-1 11 12 13 14
2001-1-2 21 22 23 24
2001-1-3 31 32 33 34

如何变为如下形式

　name 2001-1-1 2001-1-2 2001-1-3
a 11 21 31
b 12 22 32
c 13 23 33
d 14 24 34

能不能不用存储过程达到目的？

[xyzhh]

2002年的考研题！我当时好像使用了三个二维数组。一个保存原数据，一个是变换过的数据，还有一个临时数据，具体怎么写忘了，自己想想吧，不难！

[dongdongxiao]

在sybase下做比较困难！不过这样做有什么意义呢？

[demiurge]

用case when语句

[tiger7403]

有表如下：
　rq a b c d
2001-1-1 11 12 13 14
2001-1-2 21 22 23 24
2001-1-3 31 32 33 34

如何变为如下形式

　name 2001-1-1 2001-1-2 2001-1-3
a 11 21 31
b 12 22 32
c 13 23 33
d 14 24 34

[tiger7403]

我来试试：
1、建立中间表temp_mid结构如下：
create table temp_mid
(
rq varchar(10) primary key,
c1 int,
c2 int,
c3 int,
c4 int
)
go

2、导入数据如下：
insert into temp_mid (rq, c1, c2, c3, c4)
values ('2001-1-1', 1, 0, 0, 0)
go

insert into temp_mid (rq, c1, c2, c3, c4)
values ('2001-1-2', 1, 0, 0, 0)
go

insert into temp_mid (rq, c1, c2, c3, c4)
values ('2001-1-3', 1, 0, 0, 0)
go

insert into temp_mid (rq, c1, c2, c3, c4)
values ('2001-1-4', 1, 0, 0, 0)
go

3、表temp_mid结果如下：
rq c1 c2 c3 c4
2001-1-1 1 0 0 0
2001-1-2 0 1 0 0
2001-1-3 0 0 1 0
2001-1-4 0 0 0 1

源表如下（假如源表为temp_test）：
　rq a b c d
2001-1-1 11 12 13 14
2001-1-2 21 22 23 24
2001-1-3 31 32 33 34

4、执行语句：
select 'a' name, sum(z1.a * z2.c1) '2001-1-1', sum(z1.a * z2.c2) '2001-1-2', sum(z1.a * z2.c3) '2001-1-3'
from temp_test z1, temp_mid z2
where z1.rq = z2.rq
union
select 'b' name, sum(z1.b * z2.c1) '2001-1-1', sum(z1.b * z2.c2) '2001-1-2', sum(z1.b * z2.c3) '2001-1-3'
from temp_test z1, temp_mid z2
where z1.rq = z2.rq
union
select 'c' name, sum(z1.c * z2.c1) '2001-1-1', sum(z1.c * z2.c2) '2001-1-2', sum(z1.c * z2.c3) '2001-1-3'
from temp_test z1, temp_mid z2
where z1.rq = z2.rq
union
select 'd' name, sum(z1.d * z2.c1) '2001-1-1', sum(z1.d * z2.c2) '2001-1-2', sum(z1.d * z2.c3) '2001-1-3'
from temp_test z1, temp_mid z2
where z1.rq = z2.rq

5、结果如下：
name 2001-1-1 2001-1-2 2001-1-3
a 11 21 31
b 12 22 32
c 13 23 33
d 14 24 34

[tiger7403]

抱歉！有点小问题。第二步脚本应该是：
2、导入数据如下：
insert into temp_mid (rq, c1, c2, c3, c4)
values ('2001-1-1', 1, 0, 0, 0)
go

insert into temp_mid (rq, c1, c2, c3, c4)
values ('2001-1-2', 0, 1, 0, 0)
go

insert into temp_mid (rq, c1, c2, c3, c4)
values ('2001-1-3', 0, 0, 1, 0)
go

insert into temp_mid (rq, c1, c2, c3, c4)
values ('2001-1-4', 0, 0, 0, 1)
go

[cai21cn]

demiurge能不能讲详细一点，洗耳恭听

[cai21cn]

回复dongdongxiao(小小鸟):我想使程序尽可能干净利索完美，避免在SQL之外做那些低效率的转换工作。为什么SQL能完成的工作要交给其他语言呢？您说是吧！

[demiurge]

insert into #t_temp
select a ,sum (case rq when 2001-1-1
a
else
0 end ) as c20010101,

sum (case rq when 2001-1-2
a
else
0 end ) as c20010102
from tab
insert into #t_temp
select b ,sum (case rq when 2001-1-1
b
else
0 end ) as c20010101,

sum (case rq when 2001-1-2
b
else
0 end ) as c20010102
from tab

select sum(c20010101), sum(c_20010102) from t_temp group by type

[hansonhx]

@_@

[developer2002]

很烦：
select 'a',sum(case rq when '2001-1-1' then a else 0 end),sum(case rq when '2001-1-2' then a else 0 end),sum (case rq when '2001-1-3' then a else 0 end)
group by 'a'
union all
select 'b',sum(case rq when '2001-1-1' then b else 0 end),sum(case rq when '2001-1-2' then b else 0 end),sum (case rq when '2001-1-3' then b else 0 end)
group by 'b'
union all
select 'c',sum(case rq when '2001-1-1' then c else 0 end),sum(case rq when '2001-1-2' then c else 0 end),sum (case rq when '2001-1-3' then c else 0 end)
group by 'c'