How to Convert Rows to Columns in Oracle

How to convert Rows to Columns in Oracle?

If you are using Oracle 10g, you can use the DECODE function to pivot the rows into columns:

CREATE TABLE doc_tab (
loan_number VARCHAR2(20),
document_type VARCHAR2(20),
document_id VARCHAR2(20)
);

INSERT INTO doc_tab VALUES('992452533663', 'Voters ID', 'XPD0355636');
INSERT INTO doc_tab VALUES('992452533663', 'Pan card', 'CHXPS5522D');
INSERT INTO doc_tab VALUES('992452533663', 'Drivers licence', 'DL-0420110141769');

COMMIT;

SELECT
loan_number,
MAX(DECODE(document_type, 'Voters ID', document_id)) AS voters_id,
MAX(DECODE(document_type, 'Pan card', document_id)) AS pan_card,
MAX(DECODE(document_type, 'Drivers licence', document_id)) AS drivers_licence
FROM
doc_tab
GROUP BY loan_number
ORDER BY loan_number;

Output:

LOAN_NUMBER   VOTERS_ID            PAN_CARD             DRIVERS_LICENCE    
------------- -------------------- -------------------- --------------------
992452533663 XPD0355636 CHXPS5522D DL-0420110141769

You can achieve the same using Oracle PIVOT clause, introduced in 11g:

SELECT *
FROM doc_tab
PIVOT (
MAX(document_id) FOR document_type IN ('Voters ID','Pan card','Drivers licence')
);

SQLFiddle example with both solutions: SQLFiddle example

Read more about pivoting here: Pivot In Oracle by Tim Hall

Oracle SQL to convert rows to columns

You can use row_number() and conditional aggregation:

select id,
max(case when seqnum = 1 then docnum end) as docnum_1,
max(case when seqnum = 1 then amount end) as amount_1,
max(case when seqnum = 2 then docnum end) as docnum_2,
max(case when seqnum = 2 then amount end) as amount_2,
max(case when seqnum = 3 then docnum end) as docnum_3,
max(case when seqnum = 3 then amount end) as amount_3
from (select t.*,
row_number() over (partition by id order by due_date desc) as seqnum
from t
where status = 1
) t
group by id;

Convert rows to column in Oracle SQL

If you are sure that there would only be 2 different values of c for every a, and min value needs to come in col c and max value needs to come in col c2, You may use below query -

SELECT tb.a,
MIN(tl.c) c,
MAX(tl.c) c2
FROM table1 tb
LEFT JOIN table2 tl ON tb.a = tl.a1
GROUP BY a;

Transpose rows to columns in Oracle Sql

Something like this?

SQL> select
2 id,
3 max(decode(things, 'Food' , descr)) as food,
4 max(decode(things, 'Cars' , descr)) as cars,
5 max(decode(things, 'Sport', descr)) as sport
6 from abc
7 group by id
8 order by id;

ID FOOD CARS SPORT
---------- ---------- ---------- ----------
1 Chicken BMW Soccer
2 Mutton Ford Tennis

SQL>

As you asked for PL/SQL, a function that returns refcursor might be one option:

SQL> create or replace function f_abc return sys_refcursor is
2 l_rc sys_refcursor;
3 begin
4 open l_rc for
5 select
6 id,
7 max(decode(things, 'Food' , descr)) as food,
8 max(decode(things, 'Cars' , descr)) as cars,
9 max(decode(things, 'Sport', descr)) as sport
10 from abc
11 group by id
12 order by id;
13 return l_rc;
14 end;
15 /

Function created.

SQL> select f_abc from dual;

F_ABC
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ID FOOD CARS SPORT
---------- ---------- ---------- ----------
1 Chicken BMW Soccer
2 Mutton Ford Tennis


SQL>

Transform Rows into Columns Oracle SQL

You can also use pivot method :

select * 
from tab
pivot(
max(value) for field in ( 'Address' as "Address",
'Name' as "Name",
'Tel' as "Tel",
'Other' as "Other" ) )

Demo

convert row data into column in oracle

If you want to have a query that gives you a variable number of columns, a way could be dynamic SQL; for example, this query will build a query that does the job, no matter the number of records:

select 
'select *
from
mapping
pivot ( max(code) for code in (' ||
listagg('''' || code || ''' AS column' || n, ',') within group (order by code) ||
'))'
from (select code, rownum n from mapping)

this gives this query:

select *
from
mapping
pivot ( max(code) for code in ('A' AS column1,'B' AS column2,'C' AS column3,'D' AS column4))

which gives:

COLUMN1 COLUMN2 COLUMN3 COLUMN4
------- ------- ------- -------
A B C D
1 row selected.

Now the issue is how would you use this; you can run a dynamic query with execute immediate, but here you don't know in advance the number of columns, so you can not fetch the result of this query into anything.

A different approach could be by generating an XML result, for example:

select 
dbms_xmlgen.getxml(
'select *
from
mapping
pivot ( max(code) for code in (' ||
listagg('''' || code || ''' AS column' || n, ',') within group (order by code) ||
'))'
)

gives:

<?xml version="1.0"?>
<ROWSET>
<ROW>
<COLUMN1>A</COLUMN1>
<COLUMN2>B</COLUMN2>
<COLUMN3>C</COLUMN3>
<COLUMN4>D</COLUMN4>
</ROW>
</ROWSET>
from (select code, rownum n from mapping)

How to convert rows to columns group by multiple columns in Oracle sql

You can enumerate rows having the same values in the first three columns with row_number(), then pivot with conditional aggregation.

Assuming that the columns are called c1 to c4:

select c1, c2, c3,
max(case when rn = 1 then c4 end) c41,
max(case when rn = 2 then c4 end) c42,
max(case when rn = 3 then c4 end) c43
from (
select t.*, row_number() over(partition by c1, c2, c3 order by c4) rn
from mytable t
) t
group by c1, c2, c3


Related Topics



Leave a reply



Submit