2.1w+
社区成员
下面语句怎么写?
表A
字段A 字段B
1 a
2 b
3 c
4 d
5 e
要实现这样怎么写呢？
字段A 1 2 3 4 5
字段B a b c d e
也就是列变成行？？？
字段A 字段B
1 a
2 b
3 c
4 d
5 e
要实现这样怎么写呢？
字段A 1 2 3 4 5
字段B a b c d e
也就是列变成行？？？
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
tian790317 2006-03-24
自己也写了一个:
declare @tt varchar(50)
declare @bb varchar(50)
declare @nn varchar(50)
declare @cc varchar(50)
set @bb=''
set @cc=''
declare crh cursor for select a,b from test
open crh
fetch next from crh into @tt,@nn
while @@fetch_status = 0
begin
set @bb=@bb+@tt
set @cc=@cc+@nn
fetch next from crh into @tt,@nn
end
select @bb union select @cc
close crh
deallocate crh
declare @tt varchar(50)
declare @bb varchar(50)
declare @nn varchar(50)
declare @cc varchar(50)
set @bb=''
set @cc=''
declare crh cursor for select a,b from test
open crh
fetch next from crh into @tt,@nn
while @@fetch_status = 0
begin
set @bb=@bb+@tt
set @cc=@cc+@nn
fetch next from crh into @tt,@nn
end
select @bb union select @cc
close crh
deallocate crh
子陌红尘 2006-03-23
--生成测试数据
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12
--生成中间数据表
declare @s varchar(8000)
set @s='create table test2(a varchar(20)'
select @s=@s+','+A+' varchar(10)' from test1
set @s=@s+')'
exec(@s)
--借助中间表实现行列转换
declare @name varchar(20)
declare t_cursor cursor for
select name from syscolumns
where id=object_id('test1') and colid>1 order by colid
open t_cursor
fetch next from t_cursor into @name
while @@fetch_status=0
begin
exec('select '+@name+' as t into test3 from test1')
set @s='insert into test2 select '''+@name+''''
select @s=@s+','''+rtrim(t)+'''' from test3
exec(@s)
exec('drop table test3')
fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor
--查看行列互换处理结果
select * from test2
/*
a x y z
---- ---- ---- ----
b 1 5 9
c 2 6 10
d 3 7 11
e 4 8 12
*/
--删除测试数据
drop table test1,test2
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12
--生成中间数据表
declare @s varchar(8000)
set @s='create table test2(a varchar(20)'
select @s=@s+','+A+' varchar(10)' from test1
set @s=@s+')'
exec(@s)
--借助中间表实现行列转换
declare @name varchar(20)
declare t_cursor cursor for
select name from syscolumns
where id=object_id('test1') and colid>1 order by colid
open t_cursor
fetch next from t_cursor into @name
while @@fetch_status=0
begin
exec('select '+@name+' as t into test3 from test1')
set @s='insert into test2 select '''+@name+''''
select @s=@s+','''+rtrim(t)+'''' from test3
exec(@s)
exec('drop table test3')
fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor
--查看行列互换处理结果
select * from test2
/*
a x y z
---- ---- ---- ----
b 1 5 9
c 2 6 10
d 3 7 11
e 4 8 12
*/
--删除测试数据
drop table test1,test2
junshi_liu 2006-03-23
create table #tmp (A bigint,B char(1))
insert into #tmp values(1,'a')
insert into #tmp values(2,'b')
insert into #tmp values(3,'c')
insert into #tmp values(4,'d')
insert into #tmp values(5,'e')
select 'A',max((case when B='a' then cast(A as varchar(10)) end)) a,
max((case when B='b' then cast(A as varchar(10)) end)) b,
max((case when B='c' then cast(A as varchar(10)) end)) c,
max((case when B='d' then cast(A as varchar(10)) end)) d,
max((case when B='e' then cast(A as varchar(10)) end)) e
from #tmp
union
select 'B',max(isnull((case when A=1 then B end),'')) a,
max(isnull((case when A=2 then B end),'')) b,
max(isnull((case when A=3 then B end),'')) c,
max(isnull((case when A=4 then B end),'')) d,
max(isnull((case when A=5 then B end),'')) e
from #tmp
insert into #tmp values(1,'a')
insert into #tmp values(2,'b')
insert into #tmp values(3,'c')
insert into #tmp values(4,'d')
insert into #tmp values(5,'e')
select 'A',max((case when B='a' then cast(A as varchar(10)) end)) a,
max((case when B='b' then cast(A as varchar(10)) end)) b,
max((case when B='c' then cast(A as varchar(10)) end)) c,
max((case when B='d' then cast(A as varchar(10)) end)) d,
max((case when B='e' then cast(A as varchar(10)) end)) e
from #tmp
union
select 'B',max(isnull((case when A=1 then B end),'')) a,
max(isnull((case when A=2 then B end),'')) b,
max(isnull((case when A=3 then B end),'')) c,
max(isnull((case when A=4 then B end),'')) d,
max(isnull((case when A=5 then B end),'')) e
from #tmp
