能否将列转换为行

数据如下：

id qty salevalue ml mll 列名
1 100 1000 10 10%
2 200 2000 20 10%
3 300 3000 30 10%
1 101 1001 10 10%
2 201 2001 20 10%
3 301 3001 30 10%
1 102 1002 10 10%
2 202 2002 20 10%
3 302 3002 30 10%

想要的数据
   
  qty 1 100 101 102
  qty 2 200 201 202
  qty 3 300 301 302
 
salevalue 1 1000 1001 1002 
salevalue 2 2000 2001 2002 
salevalue 3 3000 3001 3002
  
  ml 1 10 10 10
  ml 2 20 20 20 
  ml 3 30 30 30

不知道大家能看的懂吗。。这对我来说太有难度了。

各位发表一下意见呗

又是行列转换，楼下写了，坛子里很多。 LZ搜下看看。

SQL code

/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)： 
姓名 课程 分数 
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/

create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go

--SQL SERVER 2000 静态SQL。
select * from
(
 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end

--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns 
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')

--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t

--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。

--------------------

SQL code

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[qty] int,[salevalue] int,[ml] int,[mll] nvarchar(3))
Insert #T
select 1,100,1000,10,N'10%' union all
select 2,200,2000,20,N'10%' union all
select 3,300,3000,30,N'10%' union all
select 1,101,1001,10,N'10%' union all
select 2,201,2001,20,N'10%' union all
select 3,301,3001,30,N'10%' union all
select 1,102,1002,10,N'10%' union all
select 2,202,2002,20,N'10%' union all
select 3,302,3002,30,N'10%'
Go
;with a
as
(
Select *,NTILE(3) over( order by (select 1)) as row
from #T as a
)
select 'Qty' as ColName,a.ID,a.qty as Col1,b.qty as col2,c.qty as col3
from a
    left join a as b on b.row=a.row+1 and a.id=b.ID
    left join a as c on c.row=b.row+1 and a.id=c.ID
where a.row=1
union all
select 'salevalue' as ColName,a.ID,a.[salevalue] as Col1,b.[salevalue] as col2,c.[salevalue] as col3
from a
    left join a as b on b.row=a.row+1 and a.id=b.ID
    left join a as c on c.row=b.row+1 and a.id=c.ID
where a.row=1
union all
select 'ml' as ColName,a.ID,a.[ml] as Col1,b.[ml] as col2,c.[ml] as col3
from a
    left join a as b on b.row=a.row+1 and a.id=b.ID
    left join a as c on c.row=b.row+1 and a.id=c.ID
where a.row=1

/*
ColName    ID    Col1    col2    col3
Qty    1    100    101    102
Qty    2    200    201    202
Qty    3    300    301    302
salevalue    1    1000    1001    1002
salevalue    2    2000    2001    2002
salevalue    3    3000    3001    3002
ml    1    10    10    10
ml    2    20    20    20
ml    3    30    30    30
*/

SQL code

if not object_id('T') is null
    drop table T
Go
Create table T([id] int,[qty] int,[salevalue] int,[ml] int,[mll] nvarchar(3))
Insert T
select 1,100,1000,10,N'10%' union all
select 2,200,2000,20,N'10%' union all
select 3,300,3000,30,N'10%' union all
select 1,101,1001,10,N'10%' union all
select 2,201,2001,20,N'10%' union all
select 3,301,3001,30,N'10%' union all
select 1,102,1002,10,N'10%' union all
select 2,202,2002,20,N'10%' union all
select 3,302,3002,30,N'10%'
go
declare @s varchar(8000),@i varchar(3),@s2 varchar(8000),@s3 varchar(8000)
select top 1 @i=COUNT(*), @s='', @s2='', @s3='' from T group by ID order by count(*) desc
while @i>0
select 
    @s=',[Col'+@i+']=max(case when row='+@i+' then [qty] end)'+@s,
    @s2=',[Col'+@i+']=max(case when row='+@i+' then [qty] end)'+@s2,
    @s3=',[Col'+@i+']=max(case when row='+@i+' then [qty] end)'+@s3,
    @i=@i-1
    print @s
exec('with a
as
(select *,row=row_number()over(partition by ID order by ID)from T)
select ''Qty'' as ColName'+@s+' from a group by ID
union all 
select ''salevalue'' as ColName'+@s2+' from a group by ID
union all
select ''ml'' as ColName'+@s3+' from a group by ID')



/*
ColName    Col1    Col2    Col3
Qty    100    101    102
Qty    202    201    200
Qty    300    301    302
salevalue    100    101    102
salevalue    202    201    200
salevalue    300    301    302
ml    100    101    102
ml    202    201    200
ml    300    301    302
*/

高手太多了 我要加紧努力向你们看齐！！

多谢各位