I have a table like this:
| ID | C1 | C2 | C3 |
|---|---|---|---|
| 1 | 1 | 0 | 1 |
| 2 | 0 | 1 | 1 |
| 3 | 1 | 0 | 1 |
and I want to get to this:
| ID | Category | Category_Value |
|---|---|---|
| 1 | C1 | 1 |
| 1 | C2 | 0 |
| 1 | C3 | 1 |
| 2 | C1 | 0 |
| 2 | C2 | 1 |
| 2 | C3 | 0 |
| 3 | C1 | 1 |
| 3 | C2 | 0 |
| 3 | C3 | 1 |
How can I do it? (I will add that I need It dynamic, because today I have 40 columns next week I can have 50 columns)
Thank you!
CodePudding user response：
One straightforward approach uses a union query:
SELECT ID, 'C1' AS Category, C1 AS Category_Value FROM yourTable
UNION ALL
SELECT ID, 'C2', C2 FROM yourTable
UNION ALL
SELECT ID, 'C3', C3 FROM yourTable
ORDER BY ID, Category;
CodePudding user response：
Another dynamic option is to Use UNPIVOT -
SELECT * FROM DATA
UNPIVOT (
Category_Value FOR Category IN (C1 AS 'C1', C2 AS 'C2', C3 AS 'C3')
);
Still you have to specify the column names as a list. If you want a fully dynamic query, You should try doing this at the presentation layer instead of database layer.