如何将表输出转置为列标题？实现指定跨表结构转换咨询

需求本质是将units表中的所有name值作为新列添加到candidate表的查询结果中，新列统一填充null，以下分不同数据库给出实现方式：

静态列写法（适用于unit数量固定的场景） #

如果units表中的单元名称不会变动，直接在查询中显式声明新列即可：

MySQL/SQL Server #

SELECT 
    id,
    candidate_name,
    NULL AS unit1,
    NULL AS unit2,
    NULL AS unit3
FROM candidate;

PostgreSQL #

SELECT 
    id,
    candidate_name,
    NULL::TEXT AS unit1,
    NULL::TEXT AS unit2,
    NULL::TEXT AS unit3
FROM candidate;

动态列写法（适用于unit数量可能变化的场景） #

如果units表中的单元会新增/删除，用动态SQL自动生成列，无需手动修改查询语句：

MySQL #

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT('NULL AS ', `name`)
  ) INTO @sql
FROM units;

SET @sql = CONCAT('SELECT c.id, c.candidate_name, ', @sql, ' FROM candidate c');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server #

DECLARE @cols NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(name), ', ')
FROM units;

SET @query = N'
SELECT 
    c.id,
    c.candidate_name,
    ' + @cols + '
FROM candidate c
CROSS JOIN (SELECT 1 AS dummy) d
PIVOT (
    MAX(NULL) FOR name IN (' + @cols + ')
) p';

EXEC sp_executesql @query;

PostgreSQL #

DO $$
DECLARE
    cols TEXT;
BEGIN
    SELECT string_agg(quote_ident(name) || ' AS NULL', ', ')
    INTO cols
    FROM units;

    EXECUTE 'SELECT id, candidate_name, ' || cols || ' FROM candidate';
END $$;

内容的提问来源于stack exchange，提问作者Rishav Ghosh