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).
id | setting | value -: | :------ | :------ 1 | A | ON 1 | B | OFF 1 | C | UNKNOWN