convert row data into column in oracle

Question

I have a table mapping which has column code and values as shown below.

A
B
C
D

i want to convert this row level data into columns like

column1 column2 column3 column4

A B C D

Can any one please help here.

Also i don't want to hard code my table data in the query as data might be different every day.

https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1 — StanislavL, Jun 01 '18 at 07:57
I think you can create an storeprocedure that creates a temp table inside and return a select to that temp table. Let me know if you need help to elaborate that :) — calm, Jun 01 '18 at 08:16
Here the point is that you can have a variable number of records, so that your result will have a variable number of columns. How would you handle that? For example, without knowing in advance the columns of your result, you can not fetch values, loop over a cursor, ... Please add some information on how you need to use the result of such a query — Aleksej, Jun 01 '18 at 08:24
If the OP is insiting on a dynamic number of columns then this question is a duplicate of https://stackoverflow.com/q/15491661/1509264 — MT0, Jun 01 '18 at 08:36
It is not a duplicate question. Because XML format is no use for me. — teepu, Jun 01 '18 at 09:13
Again, how would you use such a query? Say you can use dynamic SQL to build a query that does the job, how would you use it? What do you need to do with this query? — Aleksej, Jun 01 '18 at 09:42
The outcome will be used as a column names in my INSERT statement. — teepu, Jun 01 '18 at 10:25

score 1 · Accepted Answer · answered Jun 01 '18 at 08:39

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)

score 0 · Answer 2 · answered Jun 01 '18 at 07:57

0

Check Below Query.

select listagg(CODE,' ') within group(order by CODE) CODE
from tableName

Check This Demo.

answered Jun 01 '18 at 07:57

Mr. Bhosale

3,018
1
17
34

Thanks, how ever i want each row under each column . I have updated my data. – teepu Jun 01 '18 at 08:01
@teepu answer is based on what you posted questions and data. – Mr. Bhosale Jun 01 '18 at 08:03
I have updated my expected data – teepu Jun 01 '18 at 08:04

score 0 · Answer 3 · answered Jun 01 '18 at 08:13

0

Use PIVOT:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE mapping ( code ) AS
SELECT 'A' FROM DUAL UNION ALL
SELECT 'B' FROM DUAL UNION ALL
SELECT 'C' FROM DUAL UNION ALL
SELECT 'D' FROM DUAL;

Query 1:

SELECT *
FROM   (
  SELECT ROW_NUMBER() OVER ( ORDER BY code ) rn,
         code
  FROM   mapping
)
PIVOT ( MAX( code ) FOR rn IN (
  1 AS column1,
  2 AS column2,
  3 AS column3,
  4 AS coumn4
) )

Results:

| COLUMN1 | COLUMN2 | COLUMN3 | COUMN4 |
|---------|---------|---------|--------|
|       A |       B |       C |      D |

answered Jun 01 '18 at 08:13

MT0

143,790
11
59
117

As i have only four values at present in my table we have given `1 2 3 4`. But tomorrow i could have only 3 then i need to once again change my code. Which is not possible in production environment. – teepu Jun 01 '18 at 08:15
@teepu A query has a fixed number of columns - if you do not have a fixed number of columns then you will either need to output the values as rows and transpose it to columns in your middle-ware layer (the best solution) or use dynamic SQL to create a query with the appropriate number of columns (which is slow, complicated and difficult to maintain). – MT0 Jun 01 '18 at 08:18
How can i achieve it using dynamic query? – teepu Jun 01 '18 at 08:45

convert row data into column in oracle

3 Answers3