如何在SQL Server选择中将列移动到行

Question

我有一个SQL Server 2016临时表，如下所示：

SECTION_NAME  SORT_ORDER  COL1  COL2  COL3  COL4  COL5  COL6  COL7  COL8  COL9  COL10
ONE           1           A     B     C     D     E     F     G     H     I     J
ONE           2           C     D     E     F     G     H     I     X     Y     Z

我只选择COL列，并希望每个记录的COL列都在一个单独的行上，如下所示：

SELECT * FROM #TEMPTABLE
A
B
C
D
E
etc

我该怎么做？我不太了解PIVOT，所以不确定我是否可以使用它。谢谢

Answer 1

您需要"unpivot"；我更喜欢使用交叉应用和值，比如这个

SELECT
row_number() over (order by SECTION_NAME, SORT_ORDER, ca.value) as ID
, section_name
, sort_order
, ca.value
FROM pivoted
CROSS APPLY (
VALUES 
(col1)
, (col2)
, (col3)
, (col4)
, (col5)
, (col6)
, (col7)
, (col8)
, (col9)
, (col10)
) AS ca(value)

其产生：

+----+--------------+------------+-------+
| ID | section_name | sort_order | value |
+----+--------------+------------+-------+
|  1 | ONE          |          1 | A     |
|  2 | ONE          |          1 | B     |
|  3 | ONE          |          1 | C     |
|  4 | ONE          |          1 | D     |
|  5 | ONE          |          1 | E     |
|  6 | ONE          |          1 | F     |
|  7 | ONE          |          1 | G     |
|  8 | ONE          |          1 | H     |
|  9 | ONE          |          1 | I     |
| 10 | ONE          |          1 | J     |
| 11 | ONE          |          2 | C     |
| 12 | ONE          |          2 | D     |
| 13 | ONE          |          2 | E     |
| 14 | ONE          |          2 | F     |
| 15 | ONE          |          2 | G     |
| 16 | ONE          |          2 | H     |
| 17 | ONE          |          2 | I     |
| 18 | ONE          |          2 | X     |
| 19 | ONE          |          2 | Y     |
| 20 | ONE          |          2 | Z     |
+----+--------------+------------+-------+

看这个演示