如何将单列表格转置为单行？求无聚合的简单实现方法

固定行数的极简场景（比如仅2行） #

如果你的数据行数固定，直接通过取指定行的方式拼接即可：

MySQL #

SELECT
  (SELECT value FROM your_table LIMIT 1 OFFSET 0) AS value1,
  (SELECT value FROM your_table LIMIT 1 OFFSET 1) AS value2;

SQL Server #

-- 用OFFSET FETCH语法
SELECT
  (SELECT value FROM your_table ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) AS value1,
  (SELECT value FROM your_table ORDER BY (SELECT NULL) OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY) AS value2;

PostgreSQL #

SELECT
  (SELECT value FROM your_table LIMIT 1 OFFSET 0) AS value1,
  (SELECT value FROM your_table LIMIT 1 OFFSET 1) AS value2;

行数不固定的通用场景 #

如果行数不确定，先通过窗口函数给每行生成序号，再用条件聚合或PIVOT实现转换：

MySQL（8.0+支持窗口函数） #

WITH numbered_rows AS (
  SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
  FROM your_table
)
SELECT
  MAX(CASE WHEN rn = 1 THEN value END) AS value1,
  MAX(CASE WHEN rn = 2 THEN value END) AS value2,
  -- 有更多行就继续添加对应的CASE语句
  MAX(CASE WHEN rn = 3 THEN value END) AS value3
FROM numbered_rows;

SQL Server #

WITH numbered_rows AS (
  SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
  FROM your_table
)
SELECT value1, value2, value3
FROM numbered_rows
PIVOT (
  MAX(value) FOR rn IN ([1], [2], [3]) -- 数字对应行号，可自定义列名
) AS pvt;

PostgreSQL #

WITH numbered_rows AS (
  SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
  FROM your_table
)
SELECT
  MAX(CASE WHEN rn = 1 THEN value END) AS value1,
  MAX(CASE WHEN rn = 2 THEN value END) AS value2,
  MAX(CASE WHEN rn = 3 THEN value END) AS value3
FROM numbered_rows;

注：这里用MAX()/MIN()是为了满足语法要求，每个行号对应唯一值，聚合后不会改变原数据，本质没有做真正的聚合计算。

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