oracle中列转行写法

--查询为列的原始代码
SELECT
    1 AS num,
    t.id,
    t.company_shorthand AS CountryCode
FROM
    qhyscm.tab_base_company t
WHERE
    t.company_type = '1'
    AND t.company_shorthand = 'EORLK'
UNION ALL
SELECT
    2 AS num,
    t.id,
    t.company_shorthand AS CountryCode
FROM
    qhyscm.tab_base_company t
WHERE
    t.company_type = '2'
    AND t.company_shorthand = 'IORLK'
UNION ALL
SELECT
    3 AS num,
    t.id,
    t.company_shorthand AS CountryCode
FROM
    qhyscm.tab_base_company t
WHERE
    t.company_type = '3'
    AND t.company_shorthand = 'SORLK'
UNION ALL
SELECT
    4 AS num,
    t.id,
    t.company_shorthand AS CountryCode
FROM
    qhyscm.tab_base_company t
WHERE
    t.company_type = '4'
    AND t.company_shorthand = 'LK'

 

 列转行写法

SELECT
    *
FROM
    (
    SELECT
        1 AS num, t.id
    FROM
        qhyscm.tab_base_company t
    WHERE
        t.company_type = '1'
        AND t.company_shorthand = 'EORLK'
UNION ALL
    SELECT
        2 AS num, t.id
    FROM
        qhyscm.tab_base_company t
    WHERE
        t.company_type = '2'
        AND t.company_shorthand = 'IORLK'
UNION ALL
    SELECT
        3 AS num, t.id
    FROM
        qhyscm.tab_base_company t
    WHERE
        t.company_type = '3'
        AND t.company_shorthand = 'SORLK'
UNION ALL
    SELECT
        4 AS num, t.id
    FROM
        qhyscm.tab_base_company t
    WHERE
        t.company_type = '4'
        AND t.company_shorthand = 'LK') pivot( max(id) FOR num IN ( 1 AS eor, 2 AS ior, 3 AS sor, 4 AS wl ) )
ORDER BY
    1