Convert rows into columns firstnames
Hi Team,
Need an help in writing a query as below in HR schema in oracle 12c . I need to convert the rows into columns
Employees table
First_name
department_id
Job_id
Department Id ST_MAN ST_CLERK SH_CLERK
50 joseph vignesh ratnavel
50 christine sed govinda
50 rajesh rajiv
50 gopal
Iam trying the below quey , but not able to aggrgate all the names in a column
Select department_id,
max(case when job_id='ST_MAN' then
first_name
end) ST_MAN,
max(case when job_id='ST_CLERK' then
first_name
end) ST_CLERK,
max(case when job_id='SH_CLERK' then
first_name
end) SH_CLERK
from employees
where department_id=50
group by department_id
But iam not getting all the nams in the order as shown in the output. Any help is really appreciated
Need an help in writing a query as below in HR schema in oracle 12c . I need to convert the rows into columns
Employees table
First_name
department_id
Job_id
Department Id ST_MAN ST_CLERK SH_CLERK
50 joseph vignesh ratnavel
50 christine sed govinda
50 rajesh rajiv
50 gopal
Iam trying the below quey , but not able to aggrgate all the names in a column
Select department_id,
max(case when job_id='ST_MAN' then
first_name
end) ST_MAN,
max(case when job_id='ST_CLERK' then
first_name
end) ST_CLERK,
max(case when job_id='SH_CLERK' then
first_name
end) SH_CLERK
from employees
where department_id=50
group by department_id
But iam not getting all the nams in the order as shown in the output. Any help is really appreciated
Have you tried using the PIVOT operator? That may provide functionality close to what you are asking for.
What about "order by"? Do you want the names listed in a particular order within each job?
When you say "aggregate all the names in a column" do you want multiple names on one line in each column, but comma-separated? If not,what exactly do you want the output to look like?
What about "order by"? Do you want the names listed in a particular order within each job?
When you say "aggregate all the names in a column" do you want multiple names on one line in each column, but comma-separated? If not,what exactly do you want the output to look like?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
awesome
ASKER