Transpose Select Results with Oracle

Transpose Query Result in Oracle 11g

You're close - what you want is a combination of UNPIVOT and PIVOT:

with T AS (
  select 1 as element, 1.1 as reading1, 1.2 as reading2, 1.3 as reading3 from dual union all
  select 2 as element, 2.1 as reading1, 2.2 as reading2, 2.3 as reading3 from dual union all
  select 3 as element, 3.1 as reading1, 3.2 as reading2, 3.3 as reading3 from dual 
)
select * from (
  select * from t
  unpivot (reading_value
    for reading_name in ("READING1", "READING2", "READING3")
    )
  pivot(max(reading_value) for element in (1,2,3)
  )
)
order by reading_name

This query

• converts the columns reading1, reading2, reading3 into separate rows (the name goes into reading_name, the value into reading_value); this gives us one row per (element,reading_name)
• converts the rows 1, 2*, 3 (the values for element) into columns '1', '2', '3'; this gives us one row per reading_name

UPDATE

If the list of elements is not know until run time (e.g. because the user has the option of selecting them), you need a more dynamic approach. Here's one solution that dynamically creates a SQL statement for the given list of elements and uses a sys_refcursor for the result set.

-- setup table
create table T AS 
  select 1 as element, 1.1 as reading1, 1.2 as reading2, 1.3 as reading3 from dual union all
  select 2 as element, 2.1 as reading1, 2.2 as reading2, 2.3 as reading3 from dual union all
  select 3 as element, 3.1 as reading1, 3.2 as reading2, 3.3 as reading3 from dual ;  
/

declare
  l_Elements dbms_sql.Number_Table;

  function pivot_it(p_Elements in dbms_sql.Number_Table) 
    return sys_refcursor is
      l_SQL CLOB := empty_clob();
      l_Result sys_refcursor;
    begin
      l_SQL := '
        select * from (
          select * from t 
            unpivot (reading_value
              for reading_name in ("READING1", "READING2", "READING3")
            )
          pivot(max(reading_value) for element in (';
      for i in 1 .. p_Elements.count
              loop
                  l_SQL := l_SQL || to_char(p_Elements(i)) || ',';
                end loop;
      -- remove trailing ','                
      l_SQL := regexp_replace(l_SQL, ',$');                
      l_SQL := l_SQL || ')
        )
      )';
      dbms_output.put_line(l_SQL);
      open l_Result for l_SQL;
      return l_Result;
  end;      
begin
  l_Elements(1) := 1;
  l_Elements(2) := 2;
  -- uncomment this line to get all 3 elements
  -- l_Elements(3) := 3;
  -- return the cursor into a bind variable (to be used in the host environment)
  :p_Cursor := pivot_it(l_Elements);  
end;

How you use the cursor returned from this function depends on the environment you're using - in SQL/Plus you can just print it, and most programming languages' Oracle bindings support it out-of-the-box.

CAVEAT: While this code works for the data provided, it lacks even basic error checking. This is especially important because dynamic SQL is always a possible target for SQL injection attacks.

Transpose select results with Oracle

If you want to generate the query for each call or use a hardcoded max-column-count, then you can do something like that:

WITH tab AS
(
  SELECT table_name, column_name FROM user_tab_cols WHERE column_id <= 4
) -- user_tab_cols used to provide test data, use your table instead
SELECT MAX(c1) c1,
       MAX(c2) c2,
       MAX(c3) c3,
       MAX(c4) c4
  FROM (SELECT table_name,
               DECODE( column_id, 1, column_name ) c1,
               DECODE( column_id, 2, column_name ) c2,
               DECODE( column_id, 3, column_name ) c3,
               DECODE( column_id, 4, column_name ) c4
          FROM ( SELECT table_name,
                        column_name,
                        ROW_NUMBER() OVER ( PARTITION BY table_name ORDER BY column_name ) column_id
                   FROM tab
               )
       )
 GROUP BY table_name
 ORDER BY table_name

---

If it is sufficient to get it in that form

TABLENAME1|COL1,COL2
TABLENAME2|COL1,COL2,COL3

have a look at Tom Kyte's stragg.

Oracle SQL: Transpose / rotate a table result having one row and many columns

You can unpivot the columns to get this result as follows:

select fld, val
from (
    select to_char(id) as "Id", -- convert all columns to same type 
            name as "Name",
            some_value as "Favorite color"
      from your_table
     where id = 5
    ) unpivot(val for fld in("Id", "Name", "Favorite color"));

Oracle SQL query output by Transposing Rows into Columns

It can be done with following query if there are only two valid values for name.

WITH
  tv as (select id, name, sn from app where name = 'TV'),
  cb as (select id, name, sn from app where name = 'CB')
SELECT 
  nvl(tv.id, cb.id) AS id,
  tv.name           AS name_tv,
  tv.sn             AS sn_tv,
  cb.name           AS name_cb,
  cb.sn             AS sn_cb
FROM tv
  FULL OUTER JOIN cb
    ON tv.id = cb.id;

For more values use more tricky PIVOT:

SELECT * FROM (
  SELECT id, name, sn
  FROM app
) PIVOT (
  MAX(name) as name, MAX(sn) as sn
  FOR name IN ('TV' as TV, 'CB' as CB) --put other name values there
)

Data transpose using Oracle SQL query

There are two important things to consider.

1) a SQL table has no implicite order, so you must add the order by information to uniquely define the columns of the transposed table. (I added a column ID in the table to simulate it).

2) If you are looking for a solution for fixed number of columns and rows, you may code the result in the SQL query see below (For a dynamic solution I'd not recommend to use SQL)

 select 
   (select a from transpose where id = 1) a,
   (select a from transpose where id = 2) b,
   (select a from transpose where id = 3) c
 from dual
 union all
 select 
   (select b from transpose where id = 1) a,
   (select b from transpose where id = 2) b,
   (select b from transpose where id = 3) c
 from dual
 union all
 select 
   (select c from transpose where id = 1) a,
   (select c from transpose where id = 2) b,
   (select c from transpose where id = 3) c
 from dual;

.

     A          B          C
 ---------- ---------- ----------
     1          2          3 
     4          5          6 
     7          8          9

UPDATE - solution with PIVOT

As proposed by @WW here solution with pivot

 with trans as (
 /* add one select for each column */
 select id, 'A' col_name, a col_value from transpose union all
 select id, 'B' col, b col_value from transpose union all
 select id, 'C' col, b col_value from transpose)
 select * from trans
 PIVOT (sum(col_value) col_value 
 for (id) in 
 (1 as "C1", /* add one entry for each row (ID) */
 2 as "C2",
 3 as "C3" )
 )

Transpose rows into columns using Pivot in Oracle

With pivot you could do:

select *
from your_table
pivot (
  max(coalesce(text, to_char(num)))
  for (fieldname) in ('Width' as width, 'Height' as height, 'Comments' as comments))

ID   WIDTH HEIGHT COMMENTS
---- ----- ------ ---------
1051 121   2      FP-SK/124
1170 5678  5

I've used max(coalesce(text, to_char(num))) because you have two columns to cram into one, effectively, and you need to_char() because they are different data types. If a row could have a value in both columns then the value you end up with might not be what you want, but then you'd need to define what should happen in that case.

You could also use conditional aggregation, which is what pivot does under the hood anyway; here simplified to not coalesce, on the assumption you won't have both columns populated:

select id,
  max(case when fieldname = 'Width' then text end) as width,
  max(case when fieldname = 'Height' then num end) as height,
  max(case when fieldname = 'Comments' then text end) as comments
from your_table
group by id

ID   WIDTH HEIGHT COMMENTS
---- ----- ------ ---------
1051 121        2 FP-SK/124
1170 5678       5

db<>fiddle

Notice that the height value is now a number; in the pivot version it is - and must be - a string. You can convert the result to a different data type of course.

How do we get multiple values in the output separated by commas

You can change from max() to listagg():

select *
from your_table
pivot (
  listagg(coalesce(text, to_char(num)), ',')
  for (fieldname) in ('Width' as width, 'Height' as height, 'Comments' as comments))

or

select id,
  listagg(case when fieldname = 'Width' then text end, ',') within group (order by text) as width,
  listagg(case when fieldname = 'Height' then num end, ',') within group (order by text) as height,
  listagg(case when fieldname = 'Comments' then text end, ',') within group (order by text) as comments
from your_table
group by id

which both get

  ID WIDTH      HEIGHT     COMMENTS
---- ---------- ---------- ----------
1051 121,95                Sample
1170 5678       2,5

db<>fiddle

there is a way to transpose two strings and have a table as result?

You could add the level as another column in each query, and join them together:

select c.cod, f.flag
from (
  select level as n, regexp_substr(to_char('14521;65412;65845'),'[^;]+', 1, level) as cod
  from dual
  connect BY regexp_substr(to_char('14521;65412;65845'), '[^;]+', 1, level)
  is not null
) c
join (
  select level as n, regexp_substr(to_char('1;0;1'),'[^;]+', 1, level) as flag
  from dual
  connect BY regexp_substr(to_char('1;0;1'), '[^;]+', 1, level)
  is not null
) f
on f.n = c.n

which - with outer joins - would allow for different numbers of elements; or more simply as you suggest they will always match, use the same level for both extracts:

select regexp_substr(to_char('14521;65412;65845'),'[^;]+', 1, level) as cod,
  regexp_substr(to_char('1;0;1'),'[^;]+', 1, level) as flag
from dual
connect BY regexp_substr(to_char('14521;65412;65845'), '[^;]+', 1, level)
is not null

COD   | FLAG
:---- | :---
14521 | 1   
65412 | 0   
65845 | 1   

db<>fiddle

This method of expanding a list of values also assumes you can never have null elements, in either list. Read more.

oracle transposing text value rows to columns

I find it much easier just to use conditional aggregation:

select id,
       max(case when type = 'A' then value end) as a,
       max(case when type = 'B' then value end) as b,
       max(case when type = 'C' then value end) as c
from t
group by id;

You can insert the results into a table using create table as. That should work with a pivot query as well.

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>

How Can I Flatten the Results of a SQL Query - Transposing Rows to Columns?

The simplest method is to combine these into a single column using listagg():

select OfficeLocation,
       listagg(personid, ',') within group (order by personid) as personids
from t
group by OfficeLocation;

I do not recommend putting the values in separate columns for several reasons. First, you don't know how many columns you will need. Second, you can do this using dynamic SQL, but you cannot create a view for the result. Third, the same person could -- in theory -- appear in multiple rows, but the person would likely be in different columns.

EDIT:

If you want exactly six columns, you can use conditional aggregation:

select OfficeLocation,
       max(case when seqnum = 1 then PersonId) as PersonId_1,
       max(case when seqnum = 2 then PersonId) as PersonId_2,
       max(case when seqnum = 3 then PersonId) as PersonId_3,
       max(case when seqnum = 4 then PersonId) as PersonId_4,
       max(case when seqnum = 5 then PersonId) as PersonId_5,
       max(case when seqnum = 6 then PersonId) as PersonId_6
from (select t.*,
             row_number() over (partition by OfficeLocation order by personid) as seqnum
      from t
     ) t
group by OfficeLocation;

---

Related Topics