CODEWITHSUNDEEP
teradata
monika01
monika01

Reputation: 29

Convert columns to rows Teradata

I need convert columns to rows in Teradata without TD_UNPIVOT. My table

ID |Code_1 | Code_2 | Code_3 | Code_4| 1 |1000 | 2000 | 3000 | 4000 | 1 |1000 | 2000 | 3000 | NULL | 1 |1000 | 2000 | NULL | NULL | 1 |1000 | NULL | NULL | NULL |

I need to convert Code_1, Code_2, Code_3, Code_4 to 2 columns: first column will have all Code_n (without NULL), second one will have Level of Code:

ID | Code_n | Level_of_Code
1  | 4000   | 4
1  | 3000   | 3
1  | 2000   | 2
1  | 1000   | 1

It means, than I should know when Code has NULL (in which level Code_1, Code_2, Code_3 or Code_4 and after that convert it to columns with numbers of max level where I have not NULL).

Please help me. Thank you

Upvotes: 0

Views: 3655

Answers (1)

Mike Wodarczyk
Mike Wodarczyk

Reputation: 1273

You can produce rows by using multiple select statements and doing a union all to concatenate them together.

Select id, code_1 as "code_n", 1 as "level_of_code" from your table
Union all
Select id, code_2,2
Union all
Select id, code_3,3
Union all
 Select id, code_4,4;

Upvotes: 1

Related Questions