行列互换?--生成测试数据 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 colidopen t_cursorfetch next from t_cursor into @namewhile @@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 tb(用户ID varchar(10),姓名 varchar(10),地址 varchar(10)) insert tb select '1','张三','浙江' insert tb select '2','李四','湖南' insert tb select '3','王五','广东' --转换后的表 create table tbl([序号] int,[属性名] varchar(10),[值] varchar(10)) --查询 declare @add varchar(10) declare @name varchar(10) declare @id varchar(10) declare @col varchar(10) declare cur cursor for select name from syscolumns where id=object_id('tb') open cur fetch next from cur into @col while @@fetch_status=0 begin exec('insert tbl select (select count(*) from tb where 用户ID>a.用户ID) as ''序号'','''+@col+''','+@col+' from tb a') fetch next from cur into @col end close cur deallocate cur go select * from tbl order by 序号,属性名 desc go --删除测试数据 drop table tb drop table tbl /*结果 序号 属性名 值 ----------- ---------- ---------- 0 用户ID 3 0 姓名 王五 0 地址 广东 1 用户ID 2 1 姓名 李四 1 地址 湖南 2 用户ID 1 2 姓名 张三 2 地址 浙江(所影响的行数为 9 行)*/
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 colidopen t_cursorfetch next from t_cursor into @namewhile @@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 tb(用户ID varchar(10),姓名 varchar(10),地址 varchar(10))
insert tb select '1','张三','浙江'
insert tb select '2','李四','湖南'
insert tb select '3','王五','广东'
--转换后的表
create table tbl([序号] int,[属性名] varchar(10),[值] varchar(10))
--查询
declare @add varchar(10)
declare @name varchar(10)
declare @id varchar(10)
declare @col varchar(10)
declare cur cursor for select name from syscolumns where id=object_id('tb')
open cur
fetch next from cur into @col
while @@fetch_status=0
begin
exec('insert tbl select (select count(*) from tb where 用户ID>a.用户ID) as ''序号'','''+@col+''','+@col+' from tb a')
fetch next from cur into @col
end
close cur
deallocate cur
go
select * from tbl order by 序号,属性名 desc
go
--删除测试数据
drop table tb
drop table tbl
/*结果
序号 属性名 值
----------- ---------- ----------
0 用户ID 3
0 姓名 王五
0 地址 广东
1 用户ID 2
1 姓名 李四
1 地址 湖南
2 用户ID 1
2 姓名 张三
2 地址 浙江(所影响的行数为 9 行)*/