Skip to content
Advertisement

How to merge columns and transpose in SQL?

I’m working with a custom DBMS (compliant with SQL 2011 Standard), and am trying to combine multiple columns into a single column like so, but am struggling with the syntax:

Current table:

------------------------------------------
| ID | setting_A | setting_B | setting_C |
------------------------------------------
| 1  |     ON    |    OFF    |  UNKNOWN  |
------------------------------------------

Desired query output:

------------------------------
|  ID  |  Setting  |  Value  |
------------------------------
|  1   |     A     |    ON   |
------------------------------
|  1   |     B     |   OFF   |
------------------------------
|  1   |     C     | UNKNOWN |
------------------------------

I’ve tried various IF and CASE statements, but have hit a wall. Any help would be appreciated.

Advertisement

Answer

You can unpivot with union all:

select id, 'A' setting, setting_A value from mytable
union all select id, 'B', setting_B from mytable
union all select id, 'C', setting_C from mytable

This assumes that setting_A, setting_B and setting_C are of the same datatype (otherwise, you need conversions to align the datatypes before combining the resultsets).

Demo on DB Fiddle:

id | setting | value  
-: | :------ | :------
 1 | A       | ON     
 1 | B       | OFF    
 1 | C       | UNKNOWN
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement