我有下表：
PART ATTR VAL
-------------------
A ATTR1 VAL1
A ATTR2 VAL2
A ATTR3 VAL3
A ATTR4 VAL4
A ATTR5 VAL5
B ATTR1 VAL6
B ATTR2 VAL7
B ATTR3 VAL8
B ATTR4 VAL9
B ATTR5 VAL10
我想獲得以下輸出：
PART ATTR1 DETAIL DETAIL2
-------------------------------
A VAL1 VAL2 VAL4
B VAL6 VAL7 VAL9
邏輯是：對于每個“部分”，旋轉并放置：
- 名為“ATTR1”的列中“ATTR1”的值。
- 從那些“ATTR”為“ATTR2”或“ATTR3”的條目中，將最小 VAL 放在名為“DETAIL”的列中。
- 從“ATTR”為“ATTR4”或“ATTR5”的條目中，將最小 VAL 放入名為“DETAIL2”的列中。
uj5u.com熱心網友回復：
通過CASE在資料透視的基礎中使用陳述句和分組，您可以將這些分組值用于PIVOT您的結果。如果您真的希望MIN在結果中顯示 ，您的示例輸出將與您的示例資料不匹配，因為在使用或排序任何字串時VAL10將位于 VAL9 之前。MIN
詢問
WITH
parts (part, attr, val)
AS
(SELECT 'A', 'ATTR1', 'VAL1' FROM DUAL
UNION ALL
SELECT 'A', 'ATTR2', 'VAL2' FROM DUAL
UNION ALL
SELECT 'A', 'ATTR3', 'VAL3' FROM DUAL
UNION ALL
SELECT 'A', 'ATTR4', 'VAL4' FROM DUAL
UNION ALL
SELECT 'A', 'ATTR5', 'VAL5' FROM DUAL
UNION ALL
SELECT 'B', 'ATTR1', 'VAL6' FROM DUAL
UNION ALL
SELECT 'B', 'ATTR2', 'VAL7' FROM DUAL
UNION ALL
SELECT 'B', 'ATTR3', 'VAL8' FROM DUAL
UNION ALL
SELECT 'B', 'ATTR4', 'VAL9' FROM DUAL
UNION ALL
SELECT 'B', 'ATTR5', 'VAL10' FROM DUAL)
SELECT *
FROM ( SELECT part,
CASE
WHEN attr IN ('ATTR1') THEN 'ATTR1'
WHEN attr IN ('ATTR2', 'ATTR3') THEN 'DETAIL'
WHEN attr IN ('ATTR4', 'ATTR5') THEN 'DETAIL2'
END AS attr_name,
MIN (val) AS val
FROM parts
GROUP BY part,
CASE
WHEN attr IN ('ATTR1') THEN 'ATTR1'
WHEN attr IN ('ATTR2', 'ATTR3') THEN 'DETAIL'
WHEN attr IN ('ATTR4', 'ATTR5') THEN 'DETAIL2'
END)
PIVOT (MIN (val)
FOR attr_name
IN ('ATTR1' AS attr1, 'DETAIL' AS detal, 'DETAIL2' AS detail2))
ORDER BY part;
結果
PART ATTR1 DETAL DETAIL2
_______ ________ ________ __________
A VAL1 VAL2 VAL4
B VAL6 VAL7 VAL10
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/442070.html
上一篇：ORACLE：函式