Oracle行转列、列转行的Sql语句小结

1
2

??

select?concat(id,username)?str?from?app_user
select?id||username?str?from?app_user

1
2
3
4
5
6

??

create?table?test(id?number,name?varchar2(20));
insert?into?test?values(1,'a');
insert?into?test?values(1,'b');
insert?into?test?values(1,'c');
insert?into?test?values(2,'d');
insert?into?test?values(2,'e');

1

??

select?wm_concat(name)?name?from?test;

1

??

select?replace(wm_concat(name),',','|')?from?test;

1

??

select?id,wm_concat(name)?name?from?test?group?by?id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

??

--------?适用范围：8i,9i,10g及以后版本??（?MAX?+?DECODE?）
select?id,
???????max(decode(rn,?1,?name,?null))?||
???????max(decode(rn,?2,?','?||?name,?null))?||
???????max(decode(rn,?3,?','?||?name,?null))?str
??from?(select?id,
???????????????name,
???????????????row_number()?over(partition?by?id?order?by?name)?as?rn
??????????from?test)?t
?group?by?id
?order?by?1;
--------?适用范围：8i,9i,10g及以后版本?（?ROW_NUMBER?+?LEAD?）
select?id,?str
??from?(select?id,
???????????????row_number()?over(partition?by?id?order?by?name)?as?rn,
???????????????name?||?lead(','?||?name,?1)?over(partition?by?id?order?by?name)?||
???????????????lead(','?||?name,?2)?over(partition?by?id?order?by?name)?||?
???????????????lead(','?||?name,?3)?over(partition?by?id?order?by?name)?as?str
??????????from?test)
?where?rn?=?1
?order?by?1;
--------?适用范围：10g及以后版本?（?MODEL?）
select?id,?substr(str,?2)?str
??from?test?model?return?updated?rows?partition?by(id)?dimension?by(row_number()?
??over(partition?by?id?order?by?name)?as?rn)?measures(cast(name?as?varchar2(20))?as?str)?
??rules?upsert?iterate(3)?until(presentv(str?[?iteration_number?+?2?],?1,?0)?=?0)
??(str?[?0?]?=?str?[?0?]?||?','?||?str?[?iteration_number?+?1?])
?order?by?1;
--------?适用范围：8i,9i,10g及以后版本?（?MAX?+?DECODE?）
select?t.id?id,?max(substr(sys_connect_by_path(t.name,?','),?2))?str
??from?(select?id,?name,?row_number()?over(partition?by?id?order?by?name)?rn
??????????from?test)?t
?start?with?rn?=?1
connect?by?rn?=?prior?rn?+?1
???????and?id?=?prior?id
?group?by?t.id;

1
2
3
4
5

??

/**?这里的表名默认区分大小写?*/
select?'create?or?replace?view?as?select?'?||?wm_concat(column_name)?||
???????'?from?APP_USER'?sqlStr
??from?user_tab_columns
?where?table_name?=?'APP_USER';

1

??

select?*?from?user_tab_columns

1
2
3
4
5
6
7
8
9

??

create?table?demo(id?int,name?varchar(20),nums?int);??----?创建表
insert?into?demo?values(1,?'苹果',?1000);
insert?into?demo?values(2,?'苹果',?2000);
insert?into?demo?values(3,?'苹果',?4000);
insert?into?demo?values(4,?'橘子',?5000);
insert?into?demo?values(5,?'橘子',?3000);
insert?into?demo?values(6,?'葡萄',?3500);
insert?into?demo?values(7,?'芒果',?4200);
insert?into?demo?values(8,?'芒果',?5500);

1

??

select?name,?sum(nums)?nums?from?demo?group?by?name

1

??

select?*?from?(select?name,?nums?from?demo)?pivot?(sum(nums)?for?name?in?('苹果'?苹果,?'橘子',?'葡萄',?'芒果'));

1
2
3
4
5

??

select?*
??from?(select?sum(nums)?苹果?from?demo?where?name?=?'苹果'),
???????(select?sum(nums)?橘子?from?demo?where?name?=?'橘子'),
???????(select?sum(nums)?葡萄?from?demo?where?name?=?'葡萄'),
???????(select?sum(nums)?芒果?from?demo?where?name?=?'芒果');

1
2
3
4
5
6

??

create?table?Fruit(id?int,name?varchar(20),?Q1?int,?Q2?int,?Q3?int,?Q4?int);
insert?into?Fruit?values(1,'苹果',1000,2000,3300,5000);
insert?into?Fruit?values(2,'橘子',3000,3000,3200,1500);
insert?into?Fruit?values(3,'香蕉',2500,3500,2200,2500);
insert?into?Fruit?values(4,'葡萄',1500,2500,1200,3500);
select?*?from?Fruit

1

??

select?id?,?name,?jidu,?xiaoshou?from?Fruit?unpivot?(xiaoshou?for?jidu?in?(q1,?q2,?q3,?q4)?)

1
2
3
4
5
6
7

??

select?id,?name?,'Q1'?jidu,?(select?q1?from?fruit?where?id=f.id)?xiaoshou?from?Fruit?f
union
select?id,?name?,'Q2'?jidu,?(select?q2?from?fruit?where?id=f.id)?xiaoshou?from?Fruit?f
union
select?id,?name?,'Q3'?jidu,?(select?q3?from?fruit?where?id=f.id)?xiaoshou?from?Fruit?f
union
select?id,?name?,'Q4'?jidu,?(select?q4?from?fruit?where?id=f.id)?xiaoshou?from?Fruit?f

1
2
3
4
5
6
7

??

select?*?from?(
???select?name,?nums?as?"Purchase?Frequency"
???from?demo?t
)??????????????????????????????
pivot?xml?(
???sum(nums)?for?name?in?(any)
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

create?or?replace?procedure?ljz_pivot_xml_sp(pi_table_name???varchar2,
?????????????????????????????????????????????pi_column_name??varchar2,
?????????????????????????????????????????????pi_create_table?varchar2)?as
??v_column??????nvarchar2(50);
??v_count???????number?:=?0;
??v_i???????????number;
??v_parent_node?nvarchar2(4000);
??v_child_node??nvarchar2(4000);
??v_over????????boolean?:=?false;
??v_tmp?????????nvarchar2(50);
??v_existsnode??number;
??v_sql?????????clob;
??v_name????????varchar2(30);
??v_name_xml????xmltype;
begin
??v_sql?????????:=?'select?x.*?from?'?||?pi_table_name?||
???????????????????'?a,?xmltable(''/PivotSet''?passing?a.'?||
???????????????????pi_column_name?||?'?columns?';
??v_parent_node?:=?'/PivotSet';
??v_child_node??:=?'item[1]/column[2]';
??v_i???????????:=?1;
??execute?immediate?'select?'?||?pi_column_name?||?'?from?'?||
????????????????????pi_table_name?||?'?where?rownum=1'
????into?v_name_xml;
??select?existsnode(v_name_xml,
????????????????????'/PivotSet/item['?||?to_char(v_i)?||?']/column[1]')
????into?v_existsnode
????from?dual;
??while?v_existsnode?=?1?loop
????execute?immediate?'select?substr(extractvalue('?||?pi_column_name?||
??????????????????????',?''/PivotSet/item['?||?to_char(v_i)?||?']/column[1]''),1,30)
????from?'?||?pi_table_name?||?'?x'
??????into?v_name;
????v_sql?:=?v_sql?||?'"'?||?v_name?||?'"?varchar2(30)?path?''item['?||
?????????????to_char(v_i)?||?']/column[2]'',';
????v_i???:=?v_i?+?1;
????select?existsnode(v_name_xml,
??????????????????????'/PivotSet/item['?||?to_char(v_i)?||?']/column[1]')
??????into?v_existsnode
??????from?dual;
??end?loop;
??v_sql?:=?trim(','?from?v_sql)?||?')?x';
??commit;
??select?count(1)
????into?v_count
????from?user_tab_columns
???where?table_name?=?upper(pi_create_table);
??if?v_count?=?0?then
????execute?immediate?'create?table?'?||?pi_create_table?||?'?as?'?||?v_sql;
??end?if;
end;