transpose in teradata(columns to rows viseversa)

General

transpose in teradata(columns to rows viseversa)

Hi All,

I have a requirement to convert rows into columns in teradata.

source:








Mode col1 col2 col3 col4
Air 3 1 2 10
Sea 4 5 6 7
road 6 4 1 7
train 5 9 2 1

Target required:







air  sea road train
3 4 6 5
1 5 4 9
2 6 1 2
10 7 7 1

And the requirement is for 1 to N rows.Please help me in this.

7 REPLIES

Re: transpose in teradata(columns to rows viseversa)

the requirement is attached as file

Thanks.

Teradata Employee

Re: transpose in teradata(columns to rows viseversa)

If you don't know N then you need dynamic SQL so that the number of columns would be dynamic too

Re: transpose in teradata(columns to rows viseversa)

Hi AtardecerR0j0,

I didnt understand..can you please share how i can achieve that?

Re: transpose in teradata(columns to rows viseversa)

Hi,

I attached the requirement clearly.The columns will be from week 1 to week 53 and each row willl have different tansporation mode.

please help me on this.

Teradata Employee

Re: transpose in teradata(columns to rows viseversa)

--Replace YourDB
drop table YourDB.MySource;
create multiset table YourDB.MySource(
myMode char(5),
week1 smallint,
week2 smallint,
week3 smallint,
week4 smallint,
week5 smallint,
week6 smallint,
week7 smallint,
week8 smallint,
week9 smallint,
week10 smallint,
week11 smallint,
week12 smallint,
week13 smallint,
week14 smallint,
week15 smallint,
week16 smallint,
week17 smallint,
week18 smallint,
week19 smallint,
week20 smallint,
week21 smallint,
week22 smallint,
week23 smallint,
week24 smallint,
week25 smallint,
week26 smallint,
week27 smallint,
week28 smallint,
week29 smallint,
week30 smallint,
week31 smallint,
week32 smallint,
week33 smallint,
week34 smallint,
week35 smallint,
week36 smallint,
week37 smallint,
week38 smallint,
week39 smallint,
week40 smallint,
week41 smallint,
week42 smallint,
week43 smallint,
week44 smallint,
week45 smallint,
week46 smallint,
week47 smallint,
week48 smallint,
week49 smallint,
week50 smallint,
week51 smallint,
week52 smallint,
week53 smallint
)primary index(myMode)
;

insert into YourDB.MySource('air' ,1 ,2 ,3 ,4 ,1 ,2 ,3 ,4 ,1 ,2 ,3 ,4 ,1 ,2 ,3 ,4 ,1 ,2 ,3 ,4 ,1 ,2 ,3 ,4 ,1 ,2 ,3 ,4 ,1 ,2 ,3 ,4 ,1 ,2 ,3 ,4 ,1 ,2 ,3 ,4 ,1 ,2 ,3 ,4 ,1 ,2 ,3 ,4 ,1 ,2 ,3 ,4 ,1 );
insert into YourDB.MySource('sea' ,5 ,6 ,7 ,8 ,5 ,6 ,7 ,8 ,5 ,6 ,7 ,8 ,5 ,6 ,7 ,8 ,5 ,6 ,7 ,8 ,5 ,6 ,7 ,8 ,5 ,6 ,7 ,8 ,5 ,6 ,7 ,8 ,5 ,6 ,7 ,8 ,5 ,6 ,7 ,8 ,5 ,6 ,7 ,8 ,5 ,6 ,7 ,8 ,5 ,6 ,7 ,8 ,5 );
insert into YourDB.MySource('road' ,9 ,10 ,11 ,12 ,9 ,10 ,11 ,12 ,9 ,10 ,11 ,12 ,9 ,10 ,11 ,12 ,9 ,10 ,11 ,12 ,9 ,10 ,11 ,12 ,9 ,10 ,11 ,12 ,9 ,10 ,11 ,12 ,9 ,10 ,11 ,12 ,9 ,10 ,11 ,12 ,9 ,10 ,11 ,12 ,9 ,10 ,11 ,12 ,9 ,10 ,11 ,12 ,9 );
insert into YourDB.MySource('train' ,13 ,14 ,15 ,16 ,13 ,14 ,15 ,16 ,13 ,14 ,15 ,16 ,13 ,14 ,15 ,16 ,13 ,14 ,15 ,16 ,13 ,14 ,15 ,16 ,13 ,14 ,15 ,16 ,13 ,14 ,15 ,16 ,13 ,14 ,15 ,16 ,13 ,14 ,15 ,16 ,13 ,14 ,15 ,16 ,13 ,14 ,15 ,16 ,13 ,14 ,15 ,16 ,13);

create multiset table YourDB.MyTarget(
MyPeriod char(6),
air smallint,
sea smallint,
road smallint,
train smallint
)primary index( MyPeriod )
;

--This are the columns you want to pivot, and the pivot is myMode
select columnname
from dbc.columns
where tablename='MySource'
and databasename='YourDB'
and columnname<>'myMode'
order by columnid
;

--This is the query you want to get dynamically for each column to pivot
insert into YourDB.MyTarget
select
'week1' as period,
max(case when myMode = 'air' then week1 else null end) as air,
max(case when myMode = 'sea' then week1 else null end) as sea,
max(case when myMode = 'road' then week1 else null end) as road,
max(case when myMode = 'train' then week1 else null end) as train
from YourDB.MySource
group by 1

select 'insert into YourDB.MyTarget select
'''||trim(columnname)||''' as period,
max(case when myMode = ''air'' then '||trim(columnname)||' else null end) as air,
max(case when myMode = ''sea'' then '||trim(columnname)||' else null end) as sea,
max(case when myMode = ''road'' then '||trim(columnname)||' else null end) as road,
max(case when myMode = ''train'' then '||trim(columnname)||' else null end) as train
from YourDB.MySource
group by 1;'
from dbc.columns
where tablename='MySource'
and databasename='YourDB'
and columnname<>'myMode'
order by columnid
;
Teradata Employee

Re: transpose in teradata(columns to rows viseversa)

When you get the last answerset you should resubmit that answerset as a query

Enthusiast

Re: transpose in teradata(columns to rows viseversa)

You can also try out the TD_UNPIVOT Function (available from Teradata 14.10)