求助，求助，将几个行 旋转90度
表一
张三 178cm 80kg 10 21
王五 180 90 4 23
赵六 167 77 3 44
表二
张三 王五 赵六
178 180 167
80 90 77
10 4 3
21 23 44
将表一转换成表二 （存储过程实现），可以为 表一插入数据
求大神指教 拜谢 拜谢 拜谢 拜谢 拜谢 拜谢 拜谢 拜谢
存储 数据
[解决办法]
create table #tb(name varchar(10),col1 int,col2 int,col3 int,col4 int)
insert into #tb
select '张三',178,80,10,21
union all select '王五',180,90,4,23
union all select '赵六',167,77,3,44
declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',['+rtrim(name)+']=sum(case name when '''+rtrim(name)+''' then col1 end)'
from #tb group by name
exec('select level1'+@sql+'from
(
select name,col1,1 as level1 from #tb
union all select name,col2,2 as level2 from #tb
union all select name,col3,3 as level3 from #tb
union all select name,col4,4 as level4 from #tb
)t group by level1' )
/*
level1 王五张三赵六
-----------------------------------
1180178167
2908077
34103
4232144
*/
--1. 换成存储过程
create table tb(name varchar(10),col1 int,col2 int,col3 int,col4 int)
insert into tb
select '张三',178,80,10,21
union all select '王五',180,90,4,23
union all select '赵六',167,77,3,44
--2. 创建存储过程
create proc proc_convert
as
set nocount on
declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',['+rtrim(name)+']=sum(case name when '''+rtrim(name)+''' then col1 end)'
from tb group by name
exec('select level1'+@sql+'from
(
select name,col1,1 as level1 from tb
union all select name,col2,2 as level2 from tb
union all select name,col3,3 as level3 from tb
union all select name,col4,4 as level4 from tb
)t group by level1' )
go
--3. 执行
exec proc_convert
/*
level1 王五张三赵六
-----------------------------------
1180178167
2908077
34103
4232144
*/
create table 表一
(name varchar(5),v1 varchar(5),v2 varchar(5),v3 varchar(5),v4 varchar(5))
insert into 表一
select '张三', '178', '80', '10', '21' union all
select '王五', '180', '90', '4', '23' union all
select '赵六', '167', '77', '3', '44'
select [1],[2],[3] from
(select rn,col,val from
(select row_number() over(order by getdate()) 'rn',
name,v1,v2,v3,v4 from 表一) a
unpivot(val for col in(name,v1,v2,v3,v4)) u) t
pivot(max(val) for rn in([1],[2],[3])) p
/*
1 2 3
----- ----- -----
张三 王五 赵六
178 180 167
80 90 77
10 4 3
21 23 44
(5 row(s) affected)
*/
