Home > OS >  How to reshape a Teradata table from long to wide format without aggregation?
How to reshape a Teradata table from long to wide format without aggregation?

Time:09-23

While use of Teradata's PIVOT function has been offered in answers to various questions about converting a long table to wide format, those suggestions involve the use of an aggregation function, a parameter of PIVOT.
Is there a way to reshape/rotate the data without aggregation?

Can a table like this ...

Initial Name
A Abel
A Adriana
A Alberto
B Billy
B Bob
C Carla
C Carlos
C Cesar
C Cristina

... be turned into the below?

Initial Name1 Name2 Name3 Name4
A Abel Adriana Alberto
B Billy Bob
C Carla Carlos Cesar Cristina

Here's the code I used to create the first table in Teradata 16.20:

CREATE VOLATILE TABLE MyTab (Initial varchar(1), "Name" varchar(10))
UNIQUE PRIMARY INDEX(Initial, "Name") ON COMMIT PRESERVE ROWS;

INSERT INTO MyTab values ('A','Abel');
INSERT INTO MyTab values ('A','Adriana');
INSERT INTO MyTab values ('A','Alberto');
INSERT INTO MyTab values ('B','Billy');
INSERT INTO MyTab values ('B','Bob');
INSERT INTO MyTab values ('C','Carla');
INSERT INTO MyTab values ('C','Carlos');
INSERT INTO MyTab values ('C','Cesar');
INSERT INTO MyTab values ('C','Cristina');

SELECT * FROM MyTab ORDER BY Initial, "Name";

CodePudding user response:

If you know the number of columns, you can use conditional aggregation:

select initial,
       max(case when seqnum = 1 then name end) as name1,
       max(case when seqnum = 2 then name end) as name2,
       max(case when seqnum = 3 then name end) as name3,
       max(case when seqnum = 4 then name end) as name4
from (select t.*,
             row_number() over (partition by initial order by name) as seqnum
      from mytab t
     ) t
group by initial;
  • Related