sqlserver 行列转换（将列转换为行）

直接上语句

 1 IF OBJECT_ID('tempdb.dbo.#Contact') IS NOT NULL DROP TABLE #Contact;
 2 CREATE TABLE #Contact
 3 (
 4     EmployeeID INT NOT NULL,
 5     PhoneNumber1 BIGINT,
 6     PhoneNumber2 BIGINT,
 7     PhoneNumber3 BIGINT
 8 )
 9 GO
10 INSERT #Contact
11     (EmployeeID, PhoneNumber1, PhoneNumber2, PhoneNumber3)
12 VALUES (1, 2718353881, 3385531980, 5324571342),
13         (2, 6007163571, 6875099415, 7756620787),
14         (3, 9439250939, NULL, NULL);
15 SELECT EmployeeID,
16     PhoneType,
17     PhoneValue
18 FROM #Contact c
19 UNPIVOT
20 (
21 PhoneValue
22 FOR PhoneType IN ([PhoneNumber1], [PhoneNumber2], [PhoneNumber3])
23 ) AS p;

创建的表全量查询

想要实现的结果

相当于下面语句的执行结果

 1 SELECT EmployeeID,
 2     'PhoneNumber1' AS PhoneType,
 3     c.PhoneNumber1 AS PhoneValue
 4 FROM #Contact c
 5 WHERE c.PhoneNumber1 IS NOT NULL
 6 UNION ALL
 7 SELECT EmployeeID,
 8     'PhoneNumber2' AS PhoneType,
 9     c.PhoneNumber2 AS PhoneValue
10 FROM #Contact c
11 WHERE c.PhoneNumber2 IS NOT NULL
12 UNION ALL
13 SELECT EmployeeID,
14     'PhoneNumber3' AS PhoneType,
15     c.PhoneNumber3 AS PhoneValue
16 FROM #Contact c
17 WHERE c.PhoneNumber3 IS NOT NULL
18 ORDER BY EmployeeID, PhoneType;

posted on 2021-10-21 10:12 雯烈阅读(809) 评论(0) 编辑收藏举报