How to pivot single column of a output table in SQL?

Question

I am new to coding and don't understand the Pivot function just wanted to know if someone can help me with below query.

I have a SQL Query below

select distinct hapf.position_code, pg.name
from
hr_all_positions_f hapf, PER_VALID_GRADES_F pvgf, per_grades pg
where
hapf.position_id = pvgf.position_id
and pvgf.grade_id = pg.grade_id
and hapf.position_code = 'ABCD'

This give an output as below

POSITION_CODE    NAME
ABCD             Grade03
ABCD             Grade04
ABCD             Grade05

But I want output as below

POSITION_CODE    Grade1    Grade2    Grade3
ABCD             Grade03   Grade04   Grade05

Can someone help me with the changes I need to make in my SQL query and what happens if I have another column whose value I want to Pivot ?

Thanks,

Shivam

You say the output of your query gives `Grade03, Grade04 ,Grade05`, but you say tha you need to get `Grade01, Grade02, Grade03`; is it a typo? — Aleksej, Sep 10 '18 at 08:14

score 1 · Answer 1 · answered Sep 10 '18 at 08:18

You may need:

-- test case
with yourQuery (POSITION_CODE, NAME) as (
    select 'ABCD', 'Grade01' from dual union all
    select 'ABCD', 'Grade02' from dual union all
    select 'ABCD', 'Grade03' from dual
)
-- query
select *
from yourQuery
pivot ( max (Name) for name in
        (
            'Grade01' as Grade1,
            'Grade02' as Grade2,
            'Grade03' as Grade3
         )
       )

which gives:

POSITION_CODE GRADE1  GRADE2  GRADE3 
------------- ------- ------- -------
ABCD          Grade01 Grade02 Grade03

If you need to handle some more columns, you need to edit your code, because you need to know in advance the number and name of columns of your result set:

-- test case
with yourQuery (POSITION_CODE, NAME) as (
    select 'ABCD', 'Grade01' from dual union all
    select 'ABCD', 'Grade02' from dual union all
    select 'ABCD', 'Grade03' from dual union all
    select 'ABCD', 'Grade04' from dual
)
-- query
select *
from yourQuery
pivot ( max (Name) for name in
        (
            'Grade01' as Grade1,
            'Grade02' as Grade2,
            'Grade03' as Grade3,
            'Grade04' as Grade4
         )
       )

thus getting:

POSITION_CODE GRADE1  GRADE2  GRADE3  GRADE4 
------------- ------- ------- ------- -------
ABCD          Grade01 Grade02 Grade03 Grade04

The values 'ABCD, Grade03, Grade04, Grade05' these are not constant and will change every time with different position_code which I have hard coded as of now. Will this query work in Dynamic case also ? — shivam, Sep 10 '18 at 08:34
The 'ABCD' value may vary however you want in this example, but the values 'Grade03', 'GRade04', .. can not, because they are used in the PIVOT. You may want to search for some dynamic pivot, you'll find some good answers about this. [A starting point](https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql) — Aleksej, Sep 10 '18 at 08:41

score 0 · Answer 2 · answered Sep 10 '18 at 08:18

SELECT POSITION_CODE, Grade03,Grade04,Grade05 FROM   
(SELECT POSITION_CODE, NAME, Value_TO_PIVOT FROM mytable)Tab1  
PIVOT  
(  
SUM(Value_TO_PIVOT) FOR NAME IN (Grade03,Grade04,Grade05)) AS Tab2  
ORDER BY Tab2.POSITION_CODE

You can refer this link to write a dynamic query which will be required if you do not know values you want to pivot on or values are more

score 0 · Accepted Answer · answered Sep 12 '18 at 10:57

I solved my question from hit and trail, below is the code hope it helps others.

    select position_code,
max(case when position_num = 1 and pg_num = 1 then grade_name end) as Grade1,
max(case when position_num = 1 and pg_num = 2 then grade_name end) as Grade2,
max(case when position_num = 1 and pg_num = 3 then grade_name end) as Grade3
from
(
select 
dense_rank() over (order by hapf.position_code) position_num,
dense_rank() over (partition by hapf.position_code order by pg.name) pg_num,
hapf.position_code, 
pg.name as grade_name
from
hr_all_positions_f hapf, PER_VALID_GRADES_F pvgf, per_grades pg
where
hapf.position_id = pvgf.position_id
and pvgf.grade_id = pg.grade_id
and hapf.position_code = 'ABCD'
) group by position_code

Thanks,

Shivam

How to pivot single column of a output table in SQL?

3 Answers3