Oracle 将多行合并为一行，并具有不同的标题

合并多行为一行的Oracle SQL技巧

sql
LISTAGG(column, ', ') WITHIN GROUP (ORDER BY order_column) AS concatenated_data

sql
SELECT 
    department,
    MAX(CASE WHEN rownum = 1 THEN '员工姓名' END) AS title1,
    MAX(CASE WHEN rownum = 1 THEN employee_name END) AS data1,
    MAX(CASE WHEN rownum = 1 THEN '职务' END) AS title2,
    MAX(CASE WHEN rownum = 1 THEN job_title END) AS data2
FROM (
    SELECT 
        department,
        employee_name,
        job_title,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY employee_name) AS rownum
    FROM employee_data
)
GROUP BY department;

在 Oracle 中，可以使用 LISTAGG 函数将多行数据合并为一行，并可以自定义分隔符。如果需要为不同的标题合并不同列的数据，可以分别对每一列使用 LISTAGG 函数，然后将结果组合在一起。例如，SELECT LISTAGG(column1, ', ') WITHIN GROUP (ORDER BY column1) AS title1, LISTAGG(column2, ', ') WITHIN GROUP (ORDER BY column2) AS title2 FROM your_table。这样可以为每一列生成一个不同的标题，并将多行数据合并为一行。