我有一个表,其中包含多个元素值,每个元素都有相应的输入值:
EE ELEMENT_NAME RESULT_VALUE EFFECTIVE DATE INPUT VALUE
-----------------------------------------------------------------
12 Overtime 1000 10-APR-2023 earning
12 Overtime 10 10-APR-2023 Hours
12 REGULAR_RETRO 110 10-APR-2023 earning
11 REGULAR_RETRO 120 10-apr-2023 earning
所需输出为:
EE REGULAR_RETRO OVERTIME_PAID OVERTIME_Hours_taken
----------------------------------------------------------------
12 110 1000 10
11 120
此查询返回两行:
SELECT
ee person_number,
SUM(CASE
WHEN peen.element_name IN 'REGULAR RETRO'
THEN (RESULT_VALUE)
END) REGULAR_RETRO,
SUM(CASE
WHEN peen.element_name IN 'OVERTIME' AND input_value = 'Hours'
THEN (RESULT_VALUE)
END) OVERTIME_hours,
SUM(CASE
WHEN peen.element_name IN 'OVERTIME' AND input_value = 'earning'
THEN (RESULT_VALUE)
END) OVERTIME_paid
FROM
PER_ALL_PEOPLE_F PAPF
PER_ELEMENT_NAME PEEN,
PEr_ELEMENT_TYPE PET
WHERE
PAPF.PERSON_ID = PEEN.PERSON_ID
AND PEEN.ELEMENT_ID = PET.ELEMENT_ID
AND PET.INPUT_VALUE IN ('Earning','Hours')
AND peen.element_name IN ('REGULAR RETRO', 'OVERTIME')
GROUP BY
person_number
但是这个查询为ee#12返回了两行,因为他同时输入了name - earning和hours,但是应该是一行-
EE REGULAR_RETRO OVERTIME_PAID OVERTIME_Hours_taken
-----------------------------------------------------------------
12 110 1000 0
12 110 0 10
11 120
1条答案
按热度按时间toiithl61#
如果你已经准备好了问题中的数据-你可以将行透视到ccolumns中,使用case表达式来管理数据并按EE分组聚合以获得预期的结果: