Oracle 多行變一列的方法

標籤：

多行變一列的方法有很多，覺得這個第一眼看懂了當時就用的這個辦法。

情況是這樣的。以下資料前幾列是一樣的，需要把VAT_VALUE_CHAR 的值放在同一行上。

SELECT *
FROM ps_vat_defaults defaults
WHERE defaults.vat_driver = ‘VAT_ENT_RGSTRN‘
AND defaults.vat_driver_key1 = ‘AMB19‘
AND defaults.vat_driver_key2 = ‘DEU‘
AND vat_default_type IN (‘DGS‘,
‘EUGS‘,
‘DSP‘,
‘EUSP‘);

SELECT VAT_DRIVER
, VAT_DRIVER_KEY1
, VAT_DRIVER_KEY2
, MAX( CASE WHEN VAT_DEFAULT_TYPE = ‘DGS‘ THEN VAT_VALUE_CHAR END ) AS VALUE1
, MAX(CASE WHEN VAT_DEFAULT_TYPE = ‘DSP‘ THEN VAT_VALUE_CHAR END) AS VALUE2
, MAX( CASE WHEN VAT_DEFAULT_TYPE = ‘EUGS‘ THEN VAT_VALUE_CHAR END) AS VALUE3
, MAX(CASE WHEN VAT_DEFAULT_TYPE = ‘EUSP‘ THEN VAT_VALUE_CHAR END) AS VALUE4
FROM ps_vat_defaults defaults
WHERE defaults.vat_driver = ‘VAT_ENT_RGSTRN‘
AND vat_default_type IN (‘DGS‘, ‘EUGS‘, ‘DSP‘, ‘EUSP‘)
GROUP BY VAT_DRIVER, VAT_DRIVER_KEY1, VAT_DRIVER_KEY2

wm_concat函資料說是10g之後才有的。他可以把某個欄位一列的所有值用逗號分隔的形式放在一個cell裡。

SELECT to_char(SUBSTR( wm_concat(VAT_VALUE_CHAR), 0,80))VAT_VALUE_CHAR from ps_vat_defaults defaults where defaults.vat_driver = ‘VAT_ENT_RGSTRN‘ AND defaults.vat_driver_key1=‘AMB19‘ AND defaults.vat_driver_key2=‘NLD‘ AND vat_default_type in ( ‘DGS‘,‘EUGS‘,‘DSP‘,‘EUSP‘)

結果是一行一列（SAL,PURC,ECSL,ECPR

 

oracle 行列互轉(來自www.askoracle.org整理)

1.使用case when 列轉行

　　

SELECT NAME,        MAX(CASE WHEN COURSE=‘語文‘ THEN  SCORE END) "語文",        MAX(CASE WHEN COURSE=‘數學‘ THEN  SCORE END) "數學",        MAX(CASE WHEN COURSE=‘英語‘ THEN  SCORE END) "英語",        MAX(CASE WHEN COURSE=‘物理‘ THEN  SCORE END) "物理",        SUM(SCORE) "總分" FROM stu GROUP BY NAME;

2.一行資料行轉列

SELECT NAME,   CASE    WHEN LV = 1 THEN  ‘語文‘ --常量    WHEN LV = 2 THEN  ‘數學‘ --常量    WHEN LV = 3 THEN  ‘英語‘ --常量    WHEN LV = 4 THEN  ‘物理‘ --常量   END 科目,   CASE    WHEN LV = 1 THEN langu --列名    WHEN LV = 2 THEN math--列名    WHEN LV = 3 THEN english--列名    WHEN LV = 4 THEN pycial--列名   END 成績 FROM (  SELECT * FROM course, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 4)  ) --成績對應的列數ORDER BY 1, 2; 

3.結果集轉換成一行 

--查詢每個部門的人數 SELECT DEPTNO, COUNT(1) CN FROM EMP GROUP BY DEPTNO ORDER BY 1; 

--將上面的結果轉為一行,可以使用 SUM 或者 COUNT 來求出。 SELECT SUM(CASE WHEN DEPTNO = 10 THEN 1 END) D_10,        SUM(CASE WHEN DEPTNO = 20 THEN 1 END) D_20,        SUM(CASE WHEN DEPTNO = 30 THEN 1 END) D_30  FROM EMP; --也可以使用下面的方法。 SELECT CASE WHEN DEPTNO = 10 THEN CN END D_10,        CASE WHEN DEPTNO = 20 THEN CN END D_20,        CASE WHEN DEPTNO = 30 THEN CN END D_30   FROM (SELECT DEPTNO, COUNT(1) CN FROM EMP GROUP BY DEPTNO); --和剛講的一樣，產生了三行三列資料，使用 MAX 來擷取。 SELECT MAX(CASE WHEN DEPTNO = 10 THEN CN END) D_10,        MAX(CASE WHEN DEPTNO = 20 THEN CN END) D_20,        MAX(CASE WHEN DEPTNO = 30 THEN CN END) D_30   FROM (SELECT DEPTNO, COUNT(1) CN FROM EMP GROUP BY DEPTNO);  

4.把結果集轉換成多行 

--每種職位一列，得到下面的結果集 (每種職業的列裡面有多餘的 NULL,如果使用MAX的話，一列只會取一條最大的值了)

 

SELECT MAX(CASE JOB WHEN ‘CLERK‘ THEN ENAME END) CLERK,        MAX(CASE JOB WHEN ‘ANALYST‘ THEN ENAME END) ANALYST,             MAX(CASE JOB WHEN ‘MANAGER‘ THEN ENAME END) MANAGER,        MAX(CASE JOB WHEN ‘PRESIDENT‘ THEN ENAME END) PRESIDENT,        MAX(CASE JOB WHEN ‘SALESMAN‘ THEN ENAME END) SALESMAN   FROM (SELECT ENAME,                JOB,                --每組都是從 1 開始排序,而每列裡面只有一組有資料。也就是 RN 相同的在每列裡面只有一條資料               ROW_NUMBER() OVER(PARTITION BY JOB ORDER BY ENAME) RN           FROM EMP) GROUP BY RN ORDER BY RN; 

Oracle 多行變一列的方法