如何将表中城市列名转为City列值，生成wind_direction列？

你需要的是**列转行（Unpivot）**操作，ALTER TABLE仅用于修改表结构（如增删列、修改列属性），无法直接转换数据形态，因此需要通过查询或数据迁移来实现目标结构。

通用方案（所有数据库支持） #

1. 直接查询获取目标结构数据 #

假设原表名为weather_data，执行以下SQL可直接得到转换后的结果：

SELECT Datetime, 'Milan' AS City, Milan AS wind_direction
FROM weather_data
UNION ALL
SELECT Datetime, 'London' AS City, London AS wind_direction
FROM weather_data
ORDER BY City, Datetime;

2. 创建新表并持久化转换后的数据 #

如果需要将转换结果永久保存为新表，分两步操作：

第一步：创建目标结构的新表

CREATE TABLE weather_data_unpivoted (
    Datetime DATE, -- 根据实际数据类型调整，例如DATETIME
    City VARCHAR(50),
    wind_direction INT
);

第二步：插入转换后的数据

INSERT INTO weather_data_unpivoted (Datetime, City, wind_direction)
SELECT Datetime, 'Milan' AS City, Milan AS wind_direction
FROM weather_data
UNION ALL
SELECT Datetime, 'London' AS City, London AS wind_direction
FROM weather_data;

特定数据库的简化写法 #

如果使用的是支持高级语法的数据库，可以用更简洁的方式实现：

MySQL 8.0+ #

利用JSON函数实现动态列转行：

SELECT 
    d.Datetime,
    j.City,
    j.wind_direction
FROM weather_data d
JOIN JSON_TABLE(
    JSON_OBJECT('Milan', d.Milan, 'London', d.London),
    '$.*' COLUMNS(
        City VARCHAR(50) PATH '$',
        wind_direction INT PATH '$'
    )
) j;

SQL Server/Oracle #

使用原生UNPIVOT语法：

-- SQL Server
SELECT Datetime, City, wind_direction
FROM weather_data
UNPIVOT (
    wind_direction FOR City IN (Milan, London)
) AS unpivoted_data;

-- Oracle
SELECT Datetime, City, wind_direction
FROM weather_data
UNPIVOT (
    wind_direction FOR City IN (Milan, London)
);

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