CODEWITHSUNDEEP
sql-serverpivot
Rich Uchytil
Rich Uchytil

Reputation: 509

How to move columns to rows in a SQL Server select?

I have a SQL Server 2016 temp table that looks like this:

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

I am only selecting the COL columns and want each records COL columns to be on a separate row, like this:

SELECT * FROM #TEMPTABLE

A
B
C
D
E
etc

How can I do that? I don't know PIVOT very well so not sure if I can use that. Thanks!

Upvotes: 1

Views: 262

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35563

You need to "unpivot" which I prefer to do using cross apply and values, like this

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)

which produces:

+----+--------------+------------+-------+
| 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     |
+----+--------------+------------+-------+

see this demo

Upvotes: 1

Related Questions