Transpose unknown number of columns with single row result using dynamic PL/SQL

Question

I believe there must be a simple way to achieve this, I just haven't been to figure it out. I've search for a ways but I can't find anything that doesn't invlovle aggregates,

I have a dynamic query (Oracle) that I build in a loop using configuration tables with the following form:

SELECT 
       b1.id1, 
       b2.id2,
       ...
FROM 
       table_a a, 
       table_b b1, 
       table_b b2, 
       ...
WHERE  
      a.id = a_variable
  AND (    b1.value (+)= a.column1 
       AND b2.value (+)= a.column2
       ...);

I always expect this to return a single row, otherwise throw an error. I'm looking to have a result something like this:

---------------
|ID  | VALUE  |
---------------
|ID1 |      9 |
|ID2 |      8 |
|ID3 |   NULL |
|ID4 |      6 |
|...          |
---------------

Instead of:

-----------------------------
|ID1  | ID2 | ID3 | ID4 | ...
-----------------------------
|   9 |   8 | NULL|   6 | ...
-----------------------------

Thanks in advance!!

It will be more clear if you explain what exactly is happening in the where clause — dagi12, Jul 07 '15 at 15:47
It's not that easy. What will you do with the query result then? Oracle does not expect that cursor will have variable number of columns. I'm afraid you will have to use dbms_sql package. PS: of course you should also look at PIVOT/UNPIVOT clause(for transposition), but neither of them also does variable number of columns. — ibre5041, Jul 07 '15 at 15:48
Try to write (static) select using PIVOT function. Once you have that, it is fairly easy to created the statement dynamically. — Wernfried Domscheit, Jul 07 '15 at 17:11

score 1 · Accepted Answer · answered Jul 07 '15 at 19:09

1

Just do a simple unpivot at the end of your dynamic query:

SELECT *
FROM your_query
UNPIVOT INCLUDE NULLS (
  "VALUE" FOR id IN (ID1 ,ID2 ,ID3 ,ID4)
)

Demo: http://sqlfiddle.com/#!4/10c35/2

answered Jul 07 '15 at 19:09

krokodilko

35,300
7
55
79

Thank you, this was exactly what I was looking for! – Saieden Jul 09 '15 at 07:42

score 0 · Answer 2 · answered Jul 07 '15 at 17:19

The information you give is quite low, so my answer is also just an example which I did some time ago in my application. Of course it is not the solution to your problem but perhaps it gives you an idea how to do it.

sqlstr := 'WITH t AS '||CHR(13);
sqlstr := sqlstr || '(SELECT START_TIME, WOC_ID, COUNT(WOC_ID) AS COUNT_TRIGGER '||CHR(13);
sqlstr := sqlstr || 'FROM P_KPI_DEF JOIN T_REPORT_KPI ON REKA_WOC_ID = WOC_ID '||CHR(13);
sqlstr := sqlstr || 'WHERE MO_ID = :objId ';
sqlstr := sqlstr || 'AND AGG_LEVEL = :aggLevel ';
sqlstr := sqlstr || 'GROUP BY START_TIME, WOC_ID) '||CHR(13);
sqlstr := sqlstr || 'SELECT * FROM t PIVOT (SUM(COUNT_TRIGGER) AS KPI_COUNT FOR WOC_ID IN (';
FOR aKPI IN (SELECT * FROM P_KPI_DEF) LOOP
    sqlstr := sqlstr || aKPI.WOC_ID||' AS '||aKPI.WOC_COLUMN_NAME||',';
END LOOP;
sqlstr := REGEXP_REPLACE(sqlstr, ',$', '))')||CHR(13);  
sqlstr := sqlstr || 'ORDER BY 1';
OPEN cur FOR sqlstr USING ...;

Transpose unknown number of columns with single row result using dynamic PL/SQL

2 Answers2