数据表竖表转横表

1、
WITH T AS (
SELECT
(
SELECT
CODE_DESC_1_CONTENT
FROM
TEP0002
WHERE
CODE_CLASS = 'MMKH2'
AND CODE = SGP1Z1.TMMSMASS.DUTY_UNIT) AS DUTY_UNIT,
DUTY_USER_ID,
NVL(SUM(ACT_AMONUT),0) AS ACT_AMONUT
FROM
SGP1Z1.TMMSMASS
WHERE
AFFILIATION_DATE >= '20231201' AND AFFILIATION_DATE <= '20231231'
AND ACC_DESCRIP = '2'
AND DUTY_USER_ID != ' '
AND DUTY_USER_ID IS NOT NULL
GROUP BY
DUTY_UNIT,
DUTY_USER_ID
)
SELECT T.,
ROW_NUMBER() OVER (PARTITION BY DUTY_UNIT ORDER BY ACT_AMONUT DESC) AS rn
FROM T
SELECT MAX(count()) FROM t GROUP BY DUTY_UNIT

2、WITH T AS (
SELECT
(
SELECT CODE_DESC_1_CONTENT
FROM TEP0002
WHERE CODE_CLASS = 'MMKH2'
AND CODE = SGP1Z1.TMMSMASS.DUTY_UNIT
) AS DUTY_UNIT,
DUTY_USER_ID,
NVL(SUM(ACT_AMONUT),0) AS ACT_AMONUT
FROM SGP1Z1.TMMSMASS
WHERE AFFILIATION_DATE >= '20231201' AND AFFILIATION_DATE <= '20231231'
AND ACC_DESCRIP = '2'
AND DUTY_USER_ID != ' '
AND DUTY_USER_ID IS NOT NULL
GROUP BY DUTY_UNIT, DUTY_USER_ID
)
SELECT
DUTY_UNIT AS 单位,
MAX(CASE WHEN rn = 1 THEN DUTY_USER_ID END) AS 个人,
MAX(CASE WHEN rn = 1 THEN ACT_AMONUT END) AS 金额,
MAX(CASE WHEN rn = 2 THEN DUTY_USER_ID END) AS 个人,
MAX(CASE WHEN rn = 2 THEN ACT_AMONUT END) AS 金额,
MAX(CASE WHEN rn = 3 THEN DUTY_USER_ID END) AS 个人,
MAX(CASE WHEN rn = 3 THEN ACT_AMONUT END) AS 金额
FROM (
SELECT T.*,
ROW_NUMBER() OVER (PARTITION BY DUTY_UNIT ORDER BY ACT_AMONUT DESC) AS rn
FROM T
)
GROUP BY DUTY_UNIT;