SQL query to remove multiple columns (normalize)

Question

one is often opposed with a filled-in denormalized table, which is although having a primary key, such as:

| RowID  | Column1 | Column2 | Column3 | Column4 | ...
| 'Row1' | 'a1'    | 'a2'    | 'a3'    | 'a4'    |
| 'Row2' | 'b1'    | 'b2'    | 'b3'    | 'b4'    |
| 'Row3' | 'c1'    | 'c2'    | 'c3'    | 'c4'    |
...

Question is: how to efficiently convert it into the 3nf storing the data? (ideally, let's assume that the number of columns and their names are not known)

| RowID | ColumnID | Value |
| 1     | 1        | 'a1'  |
| 1     | 2        | 'a2'  |
...

or, ideally,

| RowID  | ColumnID  | Value |
| 'Row1' | 'Column1' | 'a1'  |
| 'Row1' | 'Column2' | 'a2'  |
...

One solution I'm aware of is selecting columns one under another with a couple of UNIONs, but the script is going to look terrible in case of many columns.

Any help is much appreciated!

Best regards, Constantine.

Is it what your are trying to do? http://stackoverflow.com/questions/18991334/select-column-name-and-value-from-table — fazerty, Nov 06 '13 at 07:29

score 0 · Accepted Answer · edited May 23 '17 at 12:03

0

actually the "Unpivot" operator was what I needed.

Unpivot with column name

Best, Constantine

edited May 23 '17 at 12:03

Community

1
1

answered Feb 26 '14 at 09:42

BusinessAlchemist

694
6
16

SQL query to remove multiple columns (normalize)

1 Answers1