sql 行列转换

SQL 行列转换
以下在达梦数据库6.0中测试通过
wisgood 原创
例子中用到的两个表
课程表 course
Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号
测试表 test
Test(C#,Property,Value) --C# --课程编号,Property 属性,Value 值

create table Course(C# varchar(10),Cname varchar(10),T# varchar(10))
insert into Course values('01' , '语文' , '02')
insert into Course values('02' , '数学' , '01')
insert into Course values('03' , '英语' , '03')

[img]http://dl.iteye.com/upload/attachment/462501/530f23e4-f926-3aaf-89fc-34e412fc12f3.png[/img]



1： 从行转换成列

select c#,'cname' as property ,cname
from course
union
select c#,'t#' ,t#
from course


为了便于从列转换成行，我们把得到的数据存入test表中
insert into test(
select c#,'cname' as property ,cname
from course
union
select c#,'t#' ,t#
from course
)
Test表中数据变为

[img]http://dl.iteye.com/upload/attachment/462503/470e94c0-b2d8-3b6f-ac61-49f6b155c983.png[/img]



2： 从列转换成行
即把test表转换成course表的形式
第一种方法：通过连接的形式
select temp.c#,a.value as cname,b.value as t#
from (select distinct c# from test ) temp
left join test a on (temp.c#=a.c# and a.property='cname')
left join test b on (temp.c#=b.c# and b.property='t#')
第二种方法：通过case语句
select test.c#,
case when test.property='cname' then test.value else null end as [cname],
case when test.property='t#' then test.value else null end as [t#]
from (select distinct c# from test ) temp
join test on (test.c#=temp.c#)

此时得到的表如下，不符合条件，需要进一步处理


[img]http://dl.iteye.com/upload/attachment/462507/29d074e2-6226-3c1f-9e64-5039b0380bd5.png[/img]


select test.c#,
max(case when test.property='cname' then test.value else null end ) as [cname],
max(case when test.property='t#' then test.value else null end )as [t#]
from (select distinct c# from test ) temp
join test on (test.c#=temp.c#)
group by test.c#
得到满足条件的行

[img]http://dl.iteye.com/upload/attachment/462505/b79b7e5d-7e95-37f8-946c-4c57c759e1ec.png[/img]