发信人: nmamtf (nmamtf), 信区: Database
标 题: Re: 【SQL问题】如何“unpivot”一个表
发信站: BBS 未名空间站 (Tue Feb 28 15:41:55 2017, 美东)
[IN ORACLE:]
CREATE TABLE MYTEST
(
ID INTEGER,
SPEND_A NUMBER (10, 2),
SPEND_B NUMBER (10, 2),
SPEND_C NUMBER (10, 2)
);
INSERT INTO MYTEST (ID,
SPEND_A,
SPEND_B,
SPEND_C)
VALUES (1,
0,
10,
0);
INSERT INTO MYTEST (ID,
SPEND_A,
SPEND_B,
SPEND_C)
VALUES (2,
25,
0,
30);
INSERT INTO MYTEST (ID,
SPEND_A,
SPEND_B,
SPEND_C)
VALUES (3,
100,
300,
80);
COMMIT;
SELECT ID,
CAT_NAME,
CASE CAT_NAME
WHEN 1 THEN SPEND_A
WHEN 2 THEN SPEND_B
WHEN 3 THEN SPEND_C
END
COL
FROM MYTEST,
( SELECT LEVEL CAT_NAME
FROM DUAL
CONNECT BY LEVEL <= 3)
WHERE ID = 1
ORDER BY ID, CAT_NAME;
Here is the result:
ID,CAT_NAME, COL
1, 1, 0
1, 2, 10
1, 3, 0
OR:
SELECT ID, CAT_NAME, VAL
FROM MYTEST
UNPIVOT
(VAL
FOR CAT_NAME
IN (SPEND_A AS 'SPEND_A',
SPEND_B AS 'SPEND_B',
SPEND_C AS 'SPEND_C'))
WHERE ID = 1;
You will get:
ID,CAT_NAME, VAL
1, SPEND_A, 0
1, SPEND_B, 10
1, SPEND_C, 0
You can use my first SQL to convert the original data table. Yes, you should
add one more ref table(s) for it.
The second one for just one time output report.
HTH
--
※ 修改:·nmamtf 於 Feb 28 16:16:37 2017 修改本文·[FROM: 152.]
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 152.]
|