Columns to rows based on naming convention

[#1]

Hello community,

I was given below SQL table data (I know it's not in NF ).

Instead of columns I want to create rows so that I have three columns width, length, height.

The number behind each column indicates their relationship.

Example:

Width1, Length1 and Height1 are related

Width2, Length2 and Height2 are related and so on.

The actual DB-table contains plenty of columns so here's just an extract:

In the end I want a table like so:

ID | Pos     | Width    | Length    | Height

1  |    1      | 10          |  2          |  10

1  |    2      | 15          |  4          |  20

1  |    3      | 20          |  6          |  30

2  |    1      | 20          |  5          |  1

2  |    2      | 40          |  10        |  2

2  |    3      | 60          |  15        |  3

Here's the data:

TableInit:

LOAD * INLINE [

    ID, Width1, Width2, Width3, Length1, Length2, Length3, Height1, Height2, Height3

    1, 10, 15, 20, 2, 4, 6, 10, 20, 30

    2, 20, 40, 60, 5, 10, 15, 1, 2, 3

];

TableInit2:

Load ID,

SubField(Width1&','&Width2&','&Width3,',') AS Width

,SubField(Length1&','&Length2&','&Length3,',') AS Length

,SubField(Height1&','&Height2&','&Height3,',') AS Height

Resident TableInit;

Thank you.

[jagan · Accepted solution]

Hi,

Try this script

Temp:

CrossTable(Type, Value)

LOAD * INLINE [

    ID, Width1, Width2, Width3, Length1, Length2, Length3, Height1, Height2, Height3

    1, 10, 15, 20, 2, 4, 6, 10, 20, 30

    2, 20, 40, 60, 5, 10, 15, 1, 2, 3

];

Data:

LOAD

ID,

Replace(Type, 'Width', '') AS Pos,

Value AS Width

RESIDENT Temp

WHERE Type Like 'Width*';

OUTER JOIN (Data)

LOAD

ID,

Replace(Type, 'Length', '') AS Pos,

Value AS Length

RESIDENT Temp

WHERE Type Like 'Length*' ;

OUTER JOIN (Data)

LOAD

ID,

Replace(Type, 'Height', '') AS Pos,

Value AS Height

RESIDENT Temp

WHERE Type Like 'Height*' ;

DROP TABLE Temp;

Regards,

Jagan.

[#3]

beautiful. Thank you.