All Forums Database
sk8s3i 35 posts Joined 06/13
21 Mar 2016
Transposing Rows to Columns

Hi All,
I know there are topics in transposing data and they have been answered. Howver, due to my lack of experience in Teradata, I was unable to use them. Hence I am posting my query in a new topic.
Tablename: OCCUPATIONS
Columns: name VARCHAR(20), occupation VARCHAR(20)
Data:
NAME                    OCCUPATION
Julia                   Actor
Maria                   Actor
Jane                    Actor
Samantha                Doctor
Jenny                   Doctor
Ketty                   Professor
Ashley                  Professor
Christeen               Professor
Priya                   Singer
Meera                   Singer
I need the output as following
Actor           Doctor           Professor           Singer
Julia           Samantha         Ketty               Priya
Maria           Jenny            Ashley              Meera
Jane            NULL             Christeen           NULL
 
Any help would be appreciated.
Thanks,
Shardul

-Thanks Shardul

sk8s3i 35 posts Joined 06/13
22 Mar 2016

Could not do it in Teradata.
However in SQL Server 2008, I found PIVOT function which worked and I got my results. However, I need it in Teradata. Can anyone please help me?
I have been trying to get it for sometime now. No luck at all :(
-Shardul

-Thanks Shardul

sakthikrr 53 posts Joined 07/12
22 Mar 2016

Hi Shardul,
When I checked few similar posts on this (http://developer.teradata.com/tag/transpose) I got some idea; But in all these post I can see some key/index/supportive columns. Please give some more details about your actual data.

create volatile table OCCUPATIONS
(
name VARCHAR(20)
,occupation VARCHAR(20)
) on commit preserve rows;

insert into occupations values('Julia','Actor');
insert into occupations values('Maria','Actor');
insert into occupations values('Jane','Actor');
insert into occupations values('Samantha','Doctor');
insert into occupations values('Jenny','Doctor');
insert into occupations values('Ketty','Professor');
insert into occupations values('Ashley','Professor');
insert into occupations values('Christeen','Professor');
insert into occupations values('Priya','Singer');
insert into occupations values('Meera','Singer');

sel * from occupations

select
max(case when OCCUPATION = 'Actor' then NAME else 0 end) as Actor,
max(case when OCCUPATION = 'Doctor' then NAME else 0 end) as Doctor,
max(case when OCCUPATION = 'Professor' then NAME else 0 end) as Professor,
max(case when OCCUPATION = 'Singer' then NAME else 0 end) as Singer
from OCCUPATIONS

 

Sakthi

sk8s3i 35 posts Joined 06/13
22 Mar 2016

Hi Sakthi,
Thanks for responding!
This is my actual data. I am trying out some code challenges to enhance my skills and that is where I came across this question.
The query which you have here in your response, I did try it earlier, however, I was getting more number of rows instead of just three (for the data in my example). That is where I am failing. I need to remove those extra NULL fields.
Thanks,
Shardul

-Thanks Shardul

chinmay_uce02 9 posts Joined 07/07
22 Mar 2016

Check if this works for you. 

SEL A.NAME AS Actor, D.NAME AS Doctor, P.NAME AS Professor, S.NAME AS Singer FROM 

(SEL NAME, RANK() OVER(PARTITION BY OCCUPATION ORDER BY NAME ASC) RNK FROM OCCUPATIONS WHERE OCCUPATION = 'Actor') A 

FULL OUTER JOIN 

(SEL NAME, RANK() OVER(PARTITION BY OCCUPATION ORDER BY NAME ASC) RNK FROM OCCUPATIONS WHERE OCCUPATION = 'Doctor') D 

ON A.RNK = D.RNK

FULL OUTER JOIN 

(SEL NAME, RANK() OVER(PARTITION BY OCCUPATION ORDER BY NAME ASC) RNK FROM OCCUPATIONS WHERE OCCUPATION = 'Professor') P 

ON A.RNK = P.RNK

FULL OUTER JOIN 

(SEL NAME, RANK() OVER(PARTITION BY OCCUPATION ORDER BY NAME ASC) RNK FROM OCCUPATIONS WHERE OCCUPATION = 'Singer') S 

ON A.RNK = S.RNK;
 

Regards,
Chinmay

sakthikrr 53 posts Joined 07/12
22 Mar 2016

Query given by Chinmay is working fine. But not sure about the performance if data size more.

Sakthi

sk8s3i 35 posts Joined 06/13
22 Mar 2016

Thanks Chinmay and Sakthi!
Query by Chinmay is working indeed, many thanks!
 
Cheers!

-Thanks Shardul

dnoeth 4628 posts Joined 11/04
23 Mar 2016

You can use a mixture between Chinmay's and Sakthi's queries:

SELECT 
   MAX(CASE WHEN OCCUPATION = 'Actor'     THEN NAME end) AS Actor,
   MAX(CASE WHEN OCCUPATION = 'Doctor'    THEN NAME end) AS Doctor,
   MAX(CASE WHEN OCCUPATION = 'Professor' THEN NAME end) AS Professor,
   MAX(CASE WHEN OCCUPATION = 'Singer'    THEN NAME end) AS Singer
FROM 
 (
   SELECT
      NAME
     ,OCCUPATION
     ,RANK() 
      OVER (PARTITION BY OCCUPATION
            ORDER BY NAME ASC) AS rnk
   FROM occupations
 ) AS dt
GROUP BY rnk
ORDER BY rnk

But this is not a task for SQL, there's no relation between those columns and the result is kind of useless, it's a kind of random display :)

Dieter

sk8s3i 35 posts Joined 06/13
17 Jun 2016

Thank you Dieter!
This query is really helpful.

-Thanks Shardul

You must sign in to leave a comment.