列變行的SQL語句

 

项目中使用的列列变行的几种方法：

第一种方法：简单易用，但是效率很低的查询方式 
 select h.hotel,
 (select a.distance from t_hotel_location_detail a  where a.location_pos=0 and a.hotel=5019) a,
 (select a.distance from t_hotel_location_detail a  where a.location_pos=1 and a.hotel=5019) b,
 (select a.distance from t_hotel_location_detail a  where a.location_pos=2 and a.hotel=5019) c,
 (select a.distance from t_hotel_location_detail a  where a.location_pos=3 and a.hotel=5019) d,
 (select a.distance from t_hotel_location_detail a  where a.location_pos=4 and a.hotel=5019) f,
  (select a.distance from t_hotel_location_detail a  where a.location_pos=5 and a.hotel=5019) d
  from t_hotel_location_detail h 
  group by h.hotel
 
 
 
 分组查询，效率相当但是巧用聚合函数的应用Max，

此处的酒店的6个方位中每一个均为一个所以使用和不使用查询数据一样巧用聚合函数，简化查询
 
 Select hotel,

 

 

 

多列變一行

a 表中有三条记录如下：

M N
1 开1
101 开2
10101 开3

如何用一条SQL将a表中的三条数据变成一条插入b表，如下：

M1 N1 M2 N2 M3 N3
1 开1 101 开2 10101 开3

 

WITH A AS (SELECT 1 M,'開1' N FROM DUAL
UNION
SELECT 101 M,'開2' N FROM DUAL
UNION
SELECT 10101 M,'開3' N FROM DUAL
)
SELECT
MAX(DECODE(C.SEQ, 1, C.M)) AS M1,
MAX(DECODE(C.SEQ, 1, C.N)) AS N1,
MAX(DECODE(C.SEQ, 2, C.M)) AS M2,
MAX(DECODE(C.SEQ, 2, C.N)) AS N2,
MAX(DECODE(C.SEQ, 3, C.M)) AS M3,
MAX(DECODE(C.SEQ, 3, C.N)) AS N3
FROM
(
SELECT A.M,
A.N,
ROW_NUMBER () OVER(PARTITION BY 1 ORDER BY A.M) AS SEQ
FROM A
) C

 

一列變一行

 

例2：

如下格式：

Col
aaaa
bbbb
cccc
....
转换后
col
aaaa,bbbb,cccc,....

with a as (select 'aaaa' co from dual
union
select 'bbbb' co from dual
union
select 'cccc' co from dual
)

方法一：
select substr(max(sys_connect_by_path(co,'->')),3) rm
from (select a.*,rownum rn from a)
start with rn=1
connect by rn-1=prior rn

方法二：

create or replace function ff (P_CO VARCHAR2)
return varchar2 is
v_co varchar2(400);
begin
for c1 in (select co from A WHERE CO=P_CO ) LOOP
v_co:=v_co||','||C1.CO;
END LOOP;
RETURN V_CO;
END ;
select MAX(FF(CO) FROM A;

 

一列變多行

WITH A AS (SELECT 'A' CD FROM DUAL
UNION
SELECT 'B' CD FROM DUAL
UNION
SELECT 'C' CD FROM DUAL
UNION
SELECT 'D' CD FROM DUAL
UNION
SELECT 'E' CD FROM DUAL
UNION
SELECT 'F' CD FROM DUAL
UNION
SELECT 'G' CD FROM DUAL
UNION
SELECT 'H' CD FROM DUAL
UNION
SELECT 'I' CD FROM DUAL
)
select
max(decode(mod(rownum, 5), 1, CD, null)) ID1,
max(decode(mod(rownum, 5), 2, CD, null)) ID2,
max(decode(mod(rownum, 5), 3, CD, null)) ID3,
max(decode(mod(rownum, 5), 4, CD, null)) ID4,
max(decode(mod(rownum, 5), 0, CD, null)) ID5
from a
group by ceil(rownum / 5)
ID1 ID2 ID3 ID4 ID5
--- --- --- --- ---
A B C D E
F G H I

SQL> WITH A AS (SELECT 1 CD1,0 CD2 FROM DUAL
2 UNION
3 SELECT 2 CD1,0 CD2 FROM DUAL
4 UNION
5 SELECT 3 CD1,0 CD2 FROM DUAL
6 UNION
7 SELECT 4 CD1,0 CD2 FROM DUAL
8 UNION
9 SELECT 5 CD1,0 CD2 FROM DUAL
10 )
11 SELECT * FROM (SELECT MAX(DECODE(RN,1,CD1,NULL)) ID1,
12 MAX(DECODE(RN,2,CD1,NULL)) ID2,
13 MAX(DECODE(RN,3,CD1,NULL)) ID3,
14 MAX(DECODE(RN,4,CD1,NULL)) ID4,
15 MAX(DECODE(RN,5,CD1,NULL)) ID5
16 FROM (SELECT CD1,CD2,ROW_NUMBER()OVER( ORDER BY CD1) RN FROM A)
17 UNION
18 SELECT MAX(DECODE(RN,1,CD2,NULL)) ID1,
19 MAX(DECODE(RN,2,CD2,NULL)) ID2,
20 MAX(DECODE(RN,3,CD2,NULL)) ID3,
21 MAX(DECODE(RN,4,CD2,NULL)) ID4,
22 MAX(DECODE(RN,5,CD2,NULL)) ID5
23 FROM (SELECT CD1,CD2,ROW_NUMBER()OVER( ORDER BY CD1) RN FROM A))
24 ORDER BY ID1 DESC
25 ;

ID1 ID2 ID3 ID4 ID5
---------- ---------- ---------- ---------- ----------
1 2 3 4 5