Oracle Rows to Column Transformation

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 transform table from row to column

One way to differentiate between valte values can be checking if string contains only digits or not (poor solution but should work):

WITH cte( vrec,valnum, valte) AS
(
  SELECT 98945823 AS vrec,   NULL AS valnum,'Total' AS valte FROM dual
  UNION ALL SELECT 98945823, NULL,  '06001'      FROM dual
  UNION ALL SELECT 98945823, 16.57,  NULL        FROM dual
  UNION ALL SELECT 98945824, NULL,  'Total'      FROM dual
  UNION ALL SELECT 98945824, NULL,  '06005'      FROM dual
  UNION ALL SELECT 98945824, 0.36,  NULL         FROM dual
)
SELECT 
    vrec
   ,MAX(CASE WHEN REGEXP_LIKE(valte, '^[[:digit:]]*$') THEN valte ELSE NULL END)
   ,MAX(CASE WHEN NOT REGEXP_LIKE(valte, '^[[:digit:]]*$') THEN valte ELSE NULL END)
   ,MAX(valnum)
FROM cte
GROUP BY vrec;

SqlFiddleDemo

Output:

╔═══════════╦═══════════════╦═══════════════╦═════════════╗
║   VREC    ║ MAX(CASE...)  ║ MAX(CASE...)  ║ MAX(VALNUM) ║
╠═══════════╬═══════════════╬═══════════════╬═════════════╣
║ 98945823  ║        06001  ║ Total         ║ 16.57       ║
║ 98945824  ║        06005  ║ Total         ║ 0.36        ║
╚═══════════╩═══════════════╩═══════════════╩═════════════╝

For your case exchange cte hardcoded values with:

select vrec, valnum, valte from val_tb where  
recd in (select recd from rectb where setd = 17)
AND (vid = 3 OR vid = 26 OR vid = 28);

Your data structure is very poor, so this solution is just workaround. You should really change underlying structure.

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

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>

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;

Oracle SQL Transforming sql rows into columns grouping by another column

One method is conditional aggregation:

select group_id,
       max(case when key = 'k1' then value end) as key1,
       max(case when key = 'k2' then value end) as key2,
       max(case when key = 'k3' then value end) as key3
from imsk
group by group_id;

Oracle Transform rows into column and combined duplicate rows

On oracle 11g (and above) pivot is the most versatile tool for this task.

The pivot_clause lets you write cross-tabulation queries that rotate
rows into columns, aggregating data in the process of the rotation.

Source

 LOCATION   |PROPERTIES |   VALUE  
 -----------|-----------|--------
 Texas      | Latitude  | 21.391 
 Texas      | Longitude | 54.12  
 Detroit    | Latitude  | 24.23  
 Detroit    | Longitude | 54.23  
 New York   | Latitude  | 24.239 
 New York   | Longitude | 55.5   

PIVOT Select

select *
  from (select Location, Properties, Value 
        from table_usa)
pivot(
   max(Value)  
   for Properties in ('Latitude' AS LATITUDE,
                      'Longitude' AS LONGITUDE)
   )
order by LATITUDE;

Results

LOCATION    | LATITUDE  |LONGITUDE
------------|-----------|---------
Texas       | 21.391    |54.12 
Detroit     | 24.23     |54.23            
New York    | 24.239    |55.5         

Oracle - How to turn rows into columns in pairs

Write a query that {1} finds all rows whose ATTRIBUTE_NAMEs ends in '_code', and {2} finds all rows whose ATTRIBUTE_NAMEs ends in '_price'. {3} JOIN the 2 result sets ON the product names, which can be found via SUBSTR().

select 
  T1.attribute_value as product_code
, T2.attribute_value as product_price
from (                                    -- {1}
  select attribute_name, attribute_value 
  from attributes_list
  where attribute_name like '%_code'
) T1 join (                               -- {2}
  select attribute_name, attribute_value 
  from attributes_list
  where attribute_name like '%_price'
) T2
  -- {3} 
  on substr( T1.attribute_name, 1, length( T1.attribute_name ) - length( '_code' ) ) 
   = substr( T2.attribute_name, 1, length( T2.attribute_name ) - length( '_price' ) )
;

-- result
   PRODUCT_CODE    PRODUCT_PRICE 
_______________ ________________ 
             10            10.99 
             11            20.99 
             12            30.99

DBfiddle here.

You can use REGEXP_REPLACE() - as in @GMB's answer - for finding the product names in the ON clause (instead of SUBSTR()).

---

Related Topics