Transposing Rows to Columns

Database
N/A

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

8 REPLIES
N/A

Re: Transposing Rows to Columns

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

Re: Transposing Rows to Columns

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
N/A

Re: Transposing Rows to Columns

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

Re: Transposing Rows to Columns

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;

Re: Transposing Rows to Columns

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

N/A

Re: Transposing Rows to Columns

Thanks Chinmay and Sakthi!

Query by Chinmay is working indeed, many thanks!

Cheers!

N/A

Re: Transposing Rows to Columns

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 :)

N/A

Re: Transposing Rows to Columns

Thank you Dieter!

This query is really helpful.