Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL select values of each selected columns on separate rows

Tags:

sql

select

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

like image 777
FableBlaze Avatar asked Jan 19 '23 22:01

FableBlaze


2 Answers

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.

like image 103
Jacob Avatar answered Mar 06 '23 22:03

Jacob


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
like image 21
Yuck Avatar answered Mar 06 '23 22:03

Yuck