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;