[Oracle]11.0之前行轉為列的方法

1. 原始數據
   SELECT B.ITEM,
   A.EMP_NO,
   A.QUESTION,
   A.TYPE,
   DECODE (A.OPTION1, ‘Y’, 1) O1,
   DECODE (A.OPTION2, ‘Y’, 2) O2,
   DECODE (A.OPTION3, ‘Y’, 3) O3,
   A.REMARK
   FROM EHRAPP.QUESTION_INVEST_LOG_T A,
   EHRAPP.QUESTION_INVEST_T B
   WHERE A.ID1 = ‘5B69608BC4C7B7ABE050920AC04317FB’
   AND A.DEL_FLAG = ‘0’
   AND A.ID1 = B.ID1
   AND A.ID2 = B.ID2
   AND B.DEL_FLAG = ‘0’
   ORDER BY A.EMP_NO, B.ITEM

2. 稍做整理
SELECT item,
emp_no,
CASE
WHEN O1 IS NOT NULL THEN O1
WHEN O2 IS NOT NULL THEN O2
WHEN O3 IS NOT NULL THEN O3
END
AS ANS,
REMARK
FROM ( SELECT B.ITEM,
A.EMP_NO,
A.QUESTION,
A.TYPE,
DECODE (A.OPTION1, ‘Y’, 1) O1,
DECODE (A.OPTION2, ‘Y’, 2) O2,
DECODE (A.OPTION3, ‘Y’, 3) O3,
A.REMARK
FROM EHRAPP.QUESTION_INVEST_LOG_T A,
EHRAPP.QUESTION_INVEST_T B
WHERE A.ID1 = ‘5B69608BC4C7B7ABE050920AC04317FB’
AND A.DEL_FLAG = ‘0’
AND A.ID1 = B.ID1
AND A.ID2 = B.ID2
AND B.DEL_FLAG = ‘0’
ORDER BY A.EMP_NO, B.ITEM)

3. 將2中按工號轉換為行
SELECT emp_no,
MAX (DECODE (item, 2, ans)) Q2, –sum,min均可
MAX (DECODE (item, 3, ans)) Q3,
MAX (DECODE (item, 5, ans)) Q5,
MAX (DECODE (item, 6, ans)) Q6,
MAX (DECODE (item, 7, ans)) Q7,
MAX (DECODE (item, 8, ans)) Q8,
MAX (DECODE (item, 9, ans)) Q9,
MAX (DECODE (item, 10, ans)) Q10,
MAX (REMARK) REMARK
FROM (SELECT item,
emp_no,
CASE
WHEN O1 IS NOT NULL THEN O1
WHEN O2 IS NOT NULL THEN O2
WHEN O3 IS NOT NULL THEN O3
END
AS ANS,
REMARK
FROM ( SELECT B.ITEM,
A.EMP_NO,
A.QUESTION,
A.TYPE,
DECODE (A.OPTION1, ‘Y’, 1) O1,
DECODE (A.OPTION2, ‘Y’, 2) O2,
DECODE (A.OPTION3, ‘Y’, 3) O3,
A.REMARK
FROM EHRAPP.QUESTION_INVEST_LOG_T A,
EHRAPP.QUESTION_INVEST_T B
WHERE A.ID1 = ‘5B69608BC4C7B7ABE050920AC04317FB’
AND A.DEL_FLAG = ‘0’
AND A.ID1 = B.ID1
AND A.ID2 = B.ID2
AND B.DEL_FLAG = ‘0’
ORDER BY A.EMP_NO, B.ITEM))
GROUP BY EMP_NO
ORDER BY EMP_NO