Select values from multiple columns into single column

Question

I have a table in a database that has 9 columns containing the same sort of data, these values are allowed to be null. I need to select each of the non-null values into a single column of values that don't care about the identity of the row from which they originated.

So, for a table that looks like this:

+---------+------+--------+------+
|   Id    | I1   | I2     | I3   | 
+---------+------+--------+------+
|    1    | x1   | x2     |  x7  |
|    2    | x3   | null   |  x8  |
|    3    | null | null   |  null|
|    4    | x4   | x5     |  null|
|    5    | null | x6     |  x9  |
+---------+------+--------+------+

I wish to select each of the values prefixed with x into a single column. My resultant data should look like the following table. The order needs to be preserved, so the first column value from the first row should be at the top and the last column value from the last row at the bottom:

+-------+
| value |
+-------+
|  x1   |
|  x2   |
|  x7   |
|  x3   |
|  x8   |
|  x4   |
|  x5   |
|  x6   |
|  x9   |
+-------+

I am using SQL Server 2008 R2. Is there a better technique for achieving this than selecting the value of each column in turn, from each row, and inserting the non-null values into the results?

Taryn · Accepted Answer · 2013-10-22T11:07:15.497

30

You can use the UNPIVOT function to get the final result:

select value
from yourtable
unpivot
(
  value
  for col in (I1, I2, I3)
) un
order by id, col;

Since you are using SQL Server 2008+, then you can also use CROSS APPLY with the VALUES clause to unpivot the columns:

select value
from yourtable
cross apply
(
    values
        ('I1', I1),
        ('I2', I2),
        ('I3', I3)
) c(col, value)
where value is not null
order by id, col

edited Oct 22 '13 at 11:07

answered Oct 22 '13 at 11:00

Taryn

242,637
56
362
405

9

The important thing to note here is that, unlike [the currently highest-voted answer](http://stackoverflow.com/a/19515927/61305), these minimize the number of full table scans that occur (1 vs. 3). And if you had 5 columns, or 8 columns, or 22 columns, that both increases the number of scans to that number, and also makes the UNION version of the query much more cumbersome to write / look at. – Aaron Bertrand Oct 22 '13 at 14:12

score 6 · Answer 2 · answered Oct 22 '13 at 10:56

6

SELECT value FROM (
   SELECT ID, 1 AS col, I1 AS [value] FROM t
   UNION ALL SELECT ID, 2,  I2 FROM t
   UNION ALL SELECT ID, 3,  I3 FROM t
) AS t WHERE value IS NOT NULL ORDER BY ID, col;

answered Oct 22 '13 at 10:56

LS_ᴅᴇᴠ

10,823
1
23
46

SELECT ID, 1 AS col, I1 AS [value] FROM t : I can't understand what this means – yuan yuan Jul 05 '21 at 02:27
@yuanyuan `1 AS col` means scalar numeric 1 in a column named "col", for data colected from I1, 2 for data from I2 and so on. It is used to ensure row order pretended by OP. – LS_ᴅᴇᴠ Jul 07 '21 at 12:51

score 5 · Answer 3 · answered Oct 22 '13 at 11:00

5

Try union like below :

    select value from
(
    select col1 as value from TestTable
    union
    select col2 as value from TestTable
    union
    select col3 as value from TestTable
) tt where value is not null

answered Oct 22 '13 at 11:00

Upendra Chaudhari

6,473
5
25
42

What is tt. Im getting errror – Kirpal Singh Dec 12 '21 at 02:45

Select values from multiple columns into single column

3 Answers3

Linked