How to split one row into multiple rows.

Database
Enthusiast

How to split one row into multiple rows.

I have a table the table contains key data + 52 multiple index values identified as index1.....index52.
I would like to write ten records from one record such as the new record will have the rows as follows:

Key value ..... index1
key value ......index2
..
..
..
key value .......index52.

I could use the union command to select each index column + key value by having 52 select statements. The idea does not appeal me. I would like another alternate way to do this.

Thanks.
2 REPLIES
Teradata Employee

Re: How to split one row into multiple rows.

You can CROSS JOIN to a table with 52 rows (e.g. just SMALLINT values 1 to 52) and use CASE to determine the value of the "INDEXn" field. Is that any better?
Enthusiast

Re: How to split one row into multiple rows.

Thanks for your great suggestion. The following query worked using your approach

/*
CROSS JOIN to a table with 52 rows (e.g. just SMALLINT values 1 to 52) and use CASE to determine the value of the "INDEXn" field. Is that any better?
*/
/*
CREATE SET TABLE DTLOADT01.test_occurrence ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
col_num SMALLINT FORMAT '99' NOT NULL)
PRIMARY INDEX ( col_num );

*/
select id_profile,
col_num,

(case col_num
when 1 then ind_seasonal01
when 2 then ind_seasonal02
when 3 then ind_seasonal03
when 4 then ind_seasonal04
when 5 then ind_seasonal05
end
) index_value

from dtmmit01.tmes022_profile_slim a,
dtloadt01.test_occurrence
order by 1, 2