Transpose a table using Oracle SQL

Question

I have some data in a table which I want to transpose using SQL. here is the sample data.

create table test_pivot(
Name varchar2(100),
DeptA varchar2(50),
DeptB varchar2(50),
DeptC varchar2(50),
DeptD varchar2(50)
);

insert all 
into test_pivot(Name,DeptA,DeptB,DeptC,DeptD)
values('Asfakul','Y',NULL,NULL,NULL)
into test_pivot(Name,DeptA,DeptB,DeptC,DeptD)
values('Debmalya',NULL,'Y',NULL,NULL)
into test_pivot(Name,DeptA,DeptB,DeptC,DeptD)
values('Ranjan',NULL,NULL,'Y',NULL)
into test_pivot(Name,DeptA,DeptB,DeptC,DeptD)
values('santanu',NULL,NULL,NULL,'Y')
select 1 from dual;

I want the data to be displayed like below..

enter image description here

I am having a tough time figuring it out. please let me know.

You know you want to pivot, so how far have you got with the pivot operator? Or are you still on an older version that doesn't support that? — Alex Poole
– Alex Poole, Commented Oct 28, 2014 at 11:20

DirkNM · Accepted Answer · 2014-10-28 12:26:55Z

Here an SELECT statement without PIVOT and UNPIVOT. As you can see, it's far more complex:

select dept,
       nvl(max(case when name = 'Asfakul' then dept_val end), 'N') as Asfakul,
       nvl(max(case when name = 'Debmalya' then dept_val end), 'N') as Debmalya,
       nvl(max(case when name = 'Ranjan' then dept_val end), 'N') as Ranjan,
       nvl(max(case when name = 'santanu' then dept_val end), 'N') as santanu 
  from(select name,
              dept,
              case when dept = 'depta' then depta
                   when dept = 'deptb' then deptb
                   when dept = 'deptc' then deptc
                   when dept = 'deptd' then deptd
               end dept_val
         from test_pivot
         join(select 'depta' as dept from dual union all
              select 'deptb' as dept from dual union all
              select 'deptc' as dept from dual union all
              select 'deptd' as dept from dual
             ) 
           on 1 = 1
      ) 
 group 
    by dept
 order
    by dept

DirkNM · Accepted Answer · 2014-10-28 12:32:20Z

1

If your DB version supports pivot and unpivot then you can use the same. See the below query, I think this should help you..

SELECT * 
  FROM(  SELECT * 
           FROM test_pivot 
        UNPIVOT (Check_val FOR DEPT IN (DEPTA, DEPTB, DEPTC, DEPTD))
      )
 PIVOT(MAX(check_val) FOR NAME IN ('Asfakul' AS Asfakul, 
                                   'Debmalya' AS Debmalya, 
                                   'Ranjan' AS Ranjan, 
                                   'santanu' AS santanu))
 ORDER BY dept;

edited Oct 28, 2014 at 12:32

DirkNM

2,66417 silver badges22 bronze badges

answered Oct 28, 2014 at 11:56

PLSQL_007

2132 silver badges8 bronze badges

3 Comments

redsoxlost Over a year ago

Thanks. But can we do the same using sql Only?

redsoxlost Over a year ago

How can we implement using the same sql.. Can you guide

redsoxlost Over a year ago

Thanks all for helping :)

Collectives™ on Stack Overflow

Transpose a table using Oracle SQL

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related