如何对含相似编号列的SQL表执行Unpivot以获取目标结果

测试表创建与初始化SQL #

drop table if exists tbl
go
create table tbl(
refId int,
refNm varchar(10),
prodID_0 int,
prodNm_0 varchar(10),
prodDesc_0 VARCHAR(10),
prodID_1 int,
prodNm_1 varchar(10),
prodDesc_1 VARCHAR(10),
prodID_2 int,
prodNm_2 varchar(10),
prodDesc_2 VARCHAR(10)
)

insert into tbl values
(1,'aaa',11,'aaa_1_1','Prod 1_1',21,'bbb_2_1','Prod 2_1',31,'ccc_3_1','Prod 3_1'),
(2,'bbb',22,'bbb_2_2','Prod 2_2',23,'bbb_2_2','Prod 2_2',34,'ccc_3_2','Prod 3_2'),
(3,'ccc',33,'ccc_3_3','Prod 3_3',24,'bbb_2_3','Prod 2_3',35,'ccc_3_3','Prod 3_3')

原表查询结果 #

目标输出 #

解决方案 #

方法一：UNION ALL（简单直观） #

针对固定分组的字段，直接拆分每组字段查询后合并：

SELECT refId, refNm, prodID_0 AS prodID, prodNm_0 AS prodNm, prodDesc_0 AS prodDesc FROM tbl
UNION ALL
SELECT refId, refNm, prodID_1 AS prodID, prodNm_1 AS prodNm, prodDesc_1 AS prodDesc FROM tbl
UNION ALL
SELECT refId, refNm, prodID_2 AS prodID, prodNm_2 AS prodNm, prodDesc_2 AS prodDesc FROM tbl
ORDER BY refId

方法二：CROSS APPLY + VALUES（扩展性强） #

如果后续有更多_n格式的字段，只需在VALUES中新增行即可：

SELECT 
    t.refId, 
    t.refNm, 
    p.prodID, 
    p.prodNm, 
    p.prodDesc
FROM tbl t
CROSS APPLY (
    VALUES
        (prodID_0, prodNm_0, prodDesc_0),
        (prodID_1, prodNm_1, prodDesc_1),
        (prodID_2, prodNm_2, prodDesc_2)
) p(prodID, prodNm, prodDesc)
ORDER BY t.refId

说明 #

普通UNPIVOT仅支持单列的行转列，无法直接处理多字段组的转换，因此需要用上述两种方式实现多字段组的宽表转长表。方法一适合字段数量少且固定的场景，方法二更适合字段数量多或后续需要扩展的场景。

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