SQL Server列转行：将Store_PartsQTY_Migration数据导入新表

问题背景 #

现有SQL Server表Store_PartsQTY_Migration，结构如下：

需将该表数据填充到新表StorePartsQTY_NewMigration，新表字段为part_no、Hand、Quantity。其中原表的Hand0列对应新表Hand字段的编号0，列值对应Quantity字段，part_no为主键，需要实现列转行操作。

实现代码 #

首先修正原代码中的语法错误（字段间缺少逗号），正确代码如下：

INSERT INTO StorePartsQTY_NewMigration (part_no, Hand, Quantity)
SELECT part_no, Hand, Quantity
FROM (
    SELECT part_no, Hand0, Hand1, Hand2, Hand3, Hand4, Hand5, Hand6, Hand7, Hand8, Hand9, Hand10, Hand11, Hand12
    FROM Store_PartsQTY_Migration
) p
UNPIVOT (
    Quantity FOR Hand IN (Hand0, Hand1, Hand2, Hand3, Hand4, Hand5, Hand6, Hand7, Hand8, Hand9, Hand10, Hand11, Hand12)
) AS unpvt;

代码说明 #

1. 内层子查询p先取出原表中所有需要转换的列，确保后续UNPIVOT能处理目标列；
2. UNPIVOT子句是核心：
   • Quantity用来接收原表中各HandX列的数值；
   • Hand用来接收原列的名称（比如Hand0、Hand1）；
3. 最后通过INSERT INTO...SELECT把转换后的行数据直接插入新表。

进阶优化：将Hand字段转为数字编号 #

如果需要把Hand字段的内容从Hand0转为纯数字0，可以修改SELECT部分的字段处理逻辑：

INSERT INTO StorePartsQTY_NewMigration (part_no, Hand, Quantity)
SELECT 
    part_no, 
    CAST(RIGHT(Hand, LEN(Hand)-4) AS INT) AS Hand, -- 截取Hand后数字并转为整数
    Quantity
FROM (
    SELECT part_no, Hand0, Hand1, Hand2, Hand3, Hand4, Hand5, Hand6, Hand7, Hand8, Hand9, Hand10, Hand11, Hand12
    FROM Store_PartsQTY_Migration
) p
UNPIVOT (
    Quantity FOR Hand IN (Hand0, Hand1, Hand2, Hand3, Hand4, Hand5, Hand6, Hand7, Hand8, Hand9, Hand10, Hand11, Hand12)
) AS unpvt;

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