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.
Columns: name VARCHAR(20), occupation VARCHAR(20)
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.
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 :(
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
) 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
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
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.
Check if this works for you.
You can use a mixture between Chinmay's and Sakthi's queries:
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
OVER (PARTITION BY OCCUPATION
ORDER BY NAME ASC) AS rnk
) 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 :)