I have a table with hundreds of rows and tens of W columns:
Column1 | Column2_W | Column3_W | ColumnX_W
123 | A | B | x
223 | A | NULL | NULL
How can i select it so that the output would be:
Column1 | W
123 | A
123 | B
123 | x
223 | A
EDIT: I am well aware that i am working with a terrible DB "design". Unfortunately i can't change it. This question is actually part of a larger problem i was handed today. I will try the given ideas tomorrow
SELECT Column1, Column2_W
FROM table
UNION ALL
SELECT Column1, Column3_W
FROM table
UNION ALL
SELECT Column1, Column4_W
FROM table
....
ORDER BY Column1
Better option: redesign your database! This looks like a spreadsheet, not a relational database.
Take a look at this article: UNPIVOT: Normalizing data on the fly
Unforunately, you're going to be stuck hand typing the column names with any of the solutions you use. Here's a sample using UNPIVOT
in SQL Server...
SELECT Column1, W
FROM YourTable
UNPIVOT (W for Column1 in (Column2_W, Column3_W /* and so on */)) AS W
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With