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
SQL (Oracle): Order by and Limit
How to Calculate a Running Total in SQL Without Using a Cursor
SQL Group by Only Rows Which Are in Sequence
How to Use Structural Annotations to Set SQL Type to Date in Model First Approach
How to Prevent a Database Trigger from Recursing
Spark SQL Queries VS Dataframe Functions
Calculate Business Days in Oracle SQL(No Functions or Procedure)
Oracle -- Split Multiple Comma Separated Values in Oracle Table to Multiple Rows
How to Do Date Math That Ignores the Year
SQL Server Convert Integer to Binary String
Can You Access the Auto Increment Value in MySQL Within One Statement
Using SQL Function Generate_Series() in Redshift
Postgresql Equivalent for Top N with Ties: Limit "With Ties"
SQL Server Convert String to Datetime