SQL - Oracle - pivoting table with dynamic data

Question

working on Oracle DB, i have the following 'GROUPS' table:

ID    NAME    LAYER    VALUE
1     A       L1       100
1     A       L2       200
1     A       L3       300
1     A       L4       400
1     A       L5       500
2     B       L1       111
2     B       L2       222
2     B       L3       333
2     B       L4       444
2     B       L5       555
2     B       L6       666
2     B       L7       777

**ID** - identifies the group.
**NAME** - group's name.
**LAYER** - a layer in the group ; a group consists of N layers.
**VALUE** - the value of a given layer in a given group.

the data in this table represents 2 elements, distinguishable by ID (1 and 2). each group contains N layers (for group 1, L1-L5 ; for group 2 L1-L7), each layer has a value.

i'm trying to create DB functionality that will pivot dynamic layers data so the each layer will become a column, and it's rows will be it's values for each unique id (1,2) in the table.

ID    NAME    L1    L2    L3    L4    L5    L6    L7    
1     A       100   200   300   400   500   
2     B       111   222   333   444   555   666   777

notice that the groups have different numbers of layers.

said functionality can be a view , a function or a stored procedure - anything really, as long as it's being handled by the DB.

Thanks very much For Your Help!

Nir

the number of layers for each group is dynamic, can be anywhere between 1 and 30 — nir weiner, May 17 '18 at 14:28
Please have a look at this blog post - it's done in SQL Server but still should be helpful https://thesqlserverdeveloper.blogspot.com/2018/02/performing-dynamic-pivot.html?view=magazine — Daniel Marcus, May 17 '18 at 14:29
Look here for an example of dynamic pivot: https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql — Art, May 17 '18 at 15:31

score 0 · Answer 1 · answered May 17 '18 at 14:39

Using Oracle's PIVOT function you can do it as follows, You have to put the list of all 30 values in the PIVOT part, I have done up to L7:

SQL> 
SQL> WITH cte_table(IDS, NAMES, LAYERS, VALUESS) as (
  2      SELECT 1, 'A', 'L1', 100 from dual union all
  3      SELECT 1, 'A', 'L2', 200 from dual union all
  4      SELECT 1, 'A', 'L3', 300 from dual union all
  5      SELECT 1, 'A', 'L4', 400 from dual union all
  6      SELECT 1, 'A', 'L5', 500 from dual union all
  7      SELECT 2, 'B', 'L1', 111 from dual union all
  8      SELECT 2, 'B', 'L2', 222 from dual union all
  9      SELECT 2, 'B', 'L3', 333 from dual union all
 10      SELECT 2, 'B', 'L4', 444 from dual union all
 11      SELECT 2, 'B', 'L5', 555 from dual union all
 12      SELECT 2, 'B', 'L6', 666 from dual union all
 13      SELECT 2, 'B', 'L7', 777 from dual)
 14  SELECT *
 15    FROM cte_table
 16   PIVOT (MIN(VALUESS) FOR layers IN ('L1' AS "L1", 'L2' AS "L2", 'L3' AS "L3", 'L4' AS "L4", 'L5' AS "L5", 'L6' AS "L6", 'L7' AS "L7")) --list goes here
 17  /

Output:

       IDS NAMES         L1         L2         L3         L4         L5         L6         L7
---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 A            100        200        300        400        500            
         2 B            111        222        333        444        555        666        777

thanks @San , but what if i have 40, or 100? what i need is an automatic way to preform this task - currently i'm doing it in a software, but i'm looking for a way to delegate this task back to the DB - this is a dynamic situation, so hard-coded solutions cannot work — nir weiner, May 17 '18 at 14:46
You could generate dynamic SQL to create a PIVOT statement but that is likely to end up looking messy quite quickly. However, it is possible. — BriteSponge, May 17 '18 at 14:59
Exactly, You cannot do it just by using PIVOT, you need to write dynamic SQL, even for XML Pivoting you need to know the number of columns to get a meaningful names. — San, May 17 '18 at 15:07

score 0 · Accepted Answer · answered May 17 '18 at 15:00

0

You can build a dynamic query like this:

decalre
    sqlstr VARCHAR2(30000);
    cur SYS_REFCURSOR;
begin

    SELECT SELECT LISTAGG(''''||LAYER||''' AS '||layer, ',') WITHIN GROUP (ORDER BY LAYER)
    INTO sqlstr 
    FROM (SELECT LAYER FROM your_table GROUP BY LAYER);

    sqlstr := 'SELECT * FROM your_table PIVOT (MIN(VALUESS) FOR layers IN ('||sqlstr||'))';
    DBMS_OUTPUT.PUT_LINE(sqlstr);

    OPEN cur FOR sqlstr;
    ...

end;

answered May 17 '18 at 15:00

Wernfried Domscheit

54,457
9
76
110

sounds interesting - will look into it. thanks @Wernfried Domscheit ! – nir weiner May 17 '18 at 20:14

SQL - Oracle - pivoting table with dynamic data

2 Answers2