oracle 行转列 举例 pivot函数
1.自己理解了一份,举例厨房客厅的长宽高,数字为虚拟,勿当真
WITH TMP AS
(SELECT 600 NUMERICAL, 'long' NAME, '厨房' PLACE
FROM DUAL
UNION ALL
SELECT 400 NUMERICAL, 'wide' NAME, '厨房' PLACE
FROM DUAL
UNION ALL
SELECT 110 NUMERICAL, 'high' NAME, '厨房' PLACE
FROM DUAL
UNION ALL
SELECT 300 NUMERICAL, 'long' NAME, '客厅' PLACE
FROM DUAL
UNION ALL
SELECT 100 NUMERICAL, 'wide' NAME, '客厅' PLACE
FROM DUAL
UNION ALL
SELECT 110 NUMERICAL, 'high' NAME, '客厅' PLACE
FROM DUAL)
SELECT *
FROM (SELECT NUMERICAL, NAME, PLACE FROM TMP)
PIVOT (MAX(NUMERICAL) -- 需要转的列的值
FOR NAME --需要转的列
IN('long' "LONG", 'wide' WIDE, 'high' "HIGH"))
ORDER BY PLACE