if exists(select 1 from sys.tables where name='test20130507')
drop table test20130507
go
create table test20130507(a varchar(20),b varchar(20),c varchar(20))
insert into test20130507
select '20130508','8:00~9:00','张三' union all
select '20130508','9:00~10:00','测试' union all
select '20130509','8:00~9:00','李四' union all
select '20130509','8:00~9:00','再测' union all
select '20130509','9:00~10:00','再测1' union all
select '20130609','9:00~10:00','再测1'
go下面一段在Oracle 里面怎么写:
declare @sql varchar(max)=''
select @sql=@sql+',max(case when a='''+a+''' then c else null end)as '''+a+''''
from (select distinct a from test20130507)a
exec ('select b'+@sql+'
from (select distinct a,b,
c=stuff((select '',''+c from test20130507 where a.a=a and a.b=b for xml path('''')),1,1,'''')
from test20130507 a)a group by b')
drop table test20130507
go
create table test20130507(a varchar(20),b varchar(20),c varchar(20))
insert into test20130507
select '20130508','8:00~9:00','张三' union all
select '20130508','9:00~10:00','测试' union all
select '20130509','8:00~9:00','李四' union all
select '20130509','8:00~9:00','再测' union all
select '20130509','9:00~10:00','再测1' union all
select '20130609','9:00~10:00','再测1'
go下面一段在Oracle 里面怎么写:
declare @sql varchar(max)=''
select @sql=@sql+',max(case when a='''+a+''' then c else null end)as '''+a+''''
from (select distinct a from test20130507)a
exec ('select b'+@sql+'
from (select distinct a,b,
c=stuff((select '',''+c from test20130507 where a.a=a and a.b=b for xml path('''')),1,1,'''')
from test20130507 a)a group by b')
select '20130508' 日期,'8:00~9:00' 时间,'张三' 姓名 from dual union all
select '20130508' 日期,'9:00~10:00' 时间,'测试' 姓名 from dual union all
select '20130509' 日期,'8:00~9:00' 时间,'李四' 姓名 from dual union all
select '20130509' 日期,'8:00~9:00' 时间,'再测' 姓名 from dual union all
select '20130509' 日期,'9:00~10:00' 时间,'再测1' 姓名 from dual union all
select '20130609' 日期,'9:00~10:00' 时间,'再测1' 姓名 from dual)
select 时间 ,listagg( 姓名,',') within group(order by 时间) from aa
group by 时间,姓名
select '20130508' 日期,'8:00~9:00' 时间,'张三' 姓名 from dual union all
select '20130508' 日期,'9:00~10:00' 时间,'测试' 姓名 from dual union all
select '20130509' 日期,'8:00~9:00' 时间,'李四' 姓名 from dual union all
select '20130509' 日期,'8:00~9:00' 时间,'再测' 姓名 from dual union all
select '20130509' 日期,'9:00~10:00' 时间,'再测1' 姓名 from dual union all
select '20130609' 日期,'9:00~10:00' 时间,'再测1' 姓名 from dual)
select 时间 ,listagg( 姓名,',') within group(order by 时间) from aa
group by 时间
上面数据仅为测试数据,真是数据日期和时间段跨度很大。是不定行列的动态sql。
7、动态行转不定列
----------------新建测试表
CREATE TABLE tmp_user_2(USER_ID NUMBER,MODE_NAME VARCHAR2(100),TYPE_ID NUmBER);----------------第一部分测试数据
INSERT INTO tmp_user_2 VALUES(1001, 'M1',1);
INSERT INTO tmp_user_2 VALUES(1001, 'M2',2);
INSERT INTO tmp_user_2 VALUES(1002, 'M1',3);
INSERT INTO tmp_user_2 VALUES(1002, 'M2',4);
INSERT INTO tmp_user_2 VALUES(1002, 'M3',5);
INSERT INTO tmp_user_2 VALUES(1003, 'M1',6);
COMMIT;----------------行转列存储过程
CREATE OR REPLACE PROCEDURE P_tmp_user_2 IS
V_SQL VARCHAR2(2000);
CURSOR CURSOR_1 IS
SELECT DISTINCT T.MODE_NAME FROM tmp_user_2 T ORDER BY MODE_NAME;BEGIN
V_SQL := 'SELECT USER_ID';
FOR V_XCLCK IN CURSOR_1 LOOP
V_SQL := V_SQL || ',' || 'SUM(DECODE(MODE_NAME,''' || V_XCLCK.MODE_NAME ||
''',TYPE_ID,0)) AS ' || V_XCLCK.MODE_NAME;
END LOOP; V_SQL := V_SQL || ' FROM tmp_user_2 GROUP BY USER_ID';
--DBMS_OUTPUT.PUT_LINE(V_SQL);
V_SQL := 'CREATE OR REPLACE VIEW tmp_user_3 AS ' || V_SQL;
--DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
END;----------------执行存储过程
BEGIN
P_tmp_user_2;
END;----------------查看结果
SELECT * FROM tmp_user_3;----------------第二部分测试数据
INSERT INTO tmp_user_2 VALUES(1003, 'M2',7);
INSERT INTO tmp_user_2 VALUES(1004, 'M5',8);
COMMIT;----------------执行存储过程
BEGIN
P_tmp_user_2;
END;
----------------查看结果
SELECT * FROM tmp_user_3;