动态语句编写行转列的SQL

动态语句编写行转列的SQL，可以防止一些列的值是未知的情况下可以使用动态语句来将行转换为列： 

举例如下：

表名：TB1
SEQ ID   NAME
----------------
1   1A     AA
2   2B     BB
3   3C     CC
需要改为

1A   2B   3C
--------------
AA   BB   CC

create table TB1
(SEQ  number,
ID    varchar2(10),
NAME  varchar2(10))

select t.* from tb1 t

select distinct A.* from(
select (case when id='1A' then name else (select name from TB1 where id='1A')  end) "1A"
      ,(case when id='2B' then name else (select name from TB1 where id='2B')  end) "2B"
      ,(case when id='3C' then name else (select name from TB1 where id='3C')  end) "3C"
 from TB1
 )A
--写成动态语句的存储过程
create or replace procedure testdm(mycursor out  sys_refcursor) is
m_sql  varchar2(4000);
m_sql1 varchar2(4000);
c sys_refcursor;
temp varchar2(10);
begin
m_sql:=' select distinct * from( select ';
OPEN c for
select distinct id from tb1;
loop
fetch c into temp;
EXIT WHEN (c%NOTFOUND);
m_sql1:=m_sql1||',(case when ID='''||temp||''' then name else (select name from TB1 where id= ''' ||temp||''' ) end) "'||temp||'" ';
END LOOP;
m_sql1:=LTRIM(m_sql1,',');
m_sql:=m_sql||m_sql1||' from TB1 ) A';
open mycursor for
m_sql;
end testdm;

 

下面的SQL语句是oracle群里一个朋友的写法，记录下来供大家分享：
SQL2000的写法：
create table aa([Name] varchar(10),[Subject] varchar(20),Result int);
insert into aa([Name],[Subject],Result)
select '张三','语文',80 union all
select '张三','数学',90 union all
select '张三','物理',85 union all
select '李四','语文',85 union all
select '李四','数学',92 union all
select '李四','物理',82

declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']' from (select distinct Subject from aa) as a
select @sql = @sql+' from aa group by name'
select @sql
exec(@sql)

drop table aa