Passing dynamic Columns to Pivot in Oracle
NickName:Rajesh Ask DateTime:2020-03-26T20:27:20

Passing dynamic Columns to Pivot in Oracle

I am new to oracle, I am trying to pass the dynamically generated column names to pivot in oracle using the below query

DECLARE
   v_cols VARCHAR2(100);
   v_query VARCHAR2(4000);

BEGIN

    SELECT LISTAGG('''' ||product_code||'''',',') WITHIN GROUP (ORDER BY product_code) 
    INTO v_cols
    FROM (
        SELECT  DISTINCT product_code
        FROM pivot_test
        );

    v_query:='
      SELECT *
      FROM   (
              SELECT product_code,
                     quantity
              FROM   pivot_test) 
              PIVOT (sum(quantity) AS qunts 
              FOR product_code IN ('|| v_cols ||'));';

    EXECUTE IMMEDIATE v_query;
    --dbms_output.Put_line(v_cols); 
    --dbms_output.Put_line(v_query);   
END;

The column generated is 'A','B','C','D' and the query generated with dynamic column is

SELECT *
FROM   (
              SELECT 
                     product_code,
                     quantity
              FROM   pivot_test) PIVOT (sum(quantity) AS qunts FOR product_code IN ('A','B','C','D'));

Result: Result

When I take the above query and run it separately it is running correctly but when I use EXECUTE IMMEDIATE v_query; I get the error

  ORA-00911: invalid character
  ORA-06512: at line 20

I am not aware what is causing the problem here can any please point me what is wrong with this dynamic query execution

Value used for testing

CREATE TABLE pivot_test
  (
     id           NUMBER,
     customer_id  NUMBER,
     product_code VARCHAR2(5),
     quantity     NUMBER
  );

INSERT INTO pivot_test VALUES (1,1,'A',10);    
INSERT INTO pivot_test VALUES (2,1,'B',20);    
INSERT INTO pivot_test VALUES (3,1,'C',30);    
INSERT INTO pivot_test VALUES (4,2,'A',40);    
INSERT INTO pivot_test VALUES (5,2,'C',50);    
INSERT INTO pivot_test VALUES (6,3,'A',60);
INSERT INTO pivot_test VALUES (7,3,'B',70);
INSERT INTO pivot_test VALUES (8,3,'C',80);    
INSERT INTO pivot_test VALUES (9,3,'D',90);    
INSERT INTO pivot_test VALUES (10,4,'A',100);

COMMIT; 

Copyright Notice:Content Author:「Rajesh」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/60867255/passing-dynamic-columns-to-pivot-in-oracle

More about “Passing dynamic Columns to Pivot in Oracle” related questions

Passing dynamic Columns to Pivot in Oracle

I am new to oracle, I am trying to pass the dynamically generated column names to pivot in oracle using the below query DECLARE v_cols VARCHAR2(100); v_query VARCHAR2(4000); BEGIN SELECT

Show Detail

Dynamic pivot in oracle sql - Procedure

I got a solution to dynamic pivot from this post . Now I want to implement the below statements in an oracle procedure. clear columns COLUMN temp_in_statement new_value str_in_statement SELECT DI...

Show Detail

Pivot with Dynamic Columns in Oracle

I am new oracle database I have a table like below ID Passengers Age Eligible 123456 Ben 65 Yes 123456 Mary 58 Yes 123458 Stephanie 37 Yes 123458 Aaron 32 Yes 123458 Caro...

Show Detail

Pivot and order multiple columns in oracle

I know how to pivot a query in oracle but i need to order the result by the columns This is the table: Customer_id Month Sales_amt Bill_amt 1 1 10 10 2 ...

Show Detail

LINQ Pivot with dynamic columns

I'm trying to create a Pivot using LINQ with dynamic columns. I have created a Pivot in SQL Server where you do not know which columns are going to get used. But don't know how to transfer that int...

Show Detail

PIVOT or PIVOT XML without listing the columns explicitly on Oracle

I am working on PL/SQL Developer v10 on Oracle 11g database. The idea is instead of writing 145 columns listed in PIVOT statement write an Pivot XML and get data from there, I am working on a basis...

Show Detail

Oracle pivot with dynamic data

I am new to the oracle database and I am trying to use PIVOT to convert rows into columns. I have following tables.. table 1 solid solname -------------- 1 xxxxxx 2 yyyyyyy tab...

Show Detail

Dynamic Pivot with varying columns

I have a POA Code dynamic pivot that pulls data from a DX temp table and inserts the data into a temp POA table. The issue I'm having is that there is a possibility of up to 35 different columns th...

Show Detail

Dynamic pivot for thousands of columns

I'm using pgAdmin III / PostgreSQL 9.4 to store and work with my data. Sample of my current data: x | y --+-- 0 | 1 1 | 1 2 | 1 5 | 2 5 | 2 2 | 2 4 | 3 6 | 3 2 | 3 How I'd like it to

Show Detail

Dynamic PIVOT with varchar columns

I'm trying to to pivot rows into columns. I basically have lots of lines where every N rows means a row on a table I'd like to list as a result set. I'll give a short example: I have a table struc...

Show Detail