By prabhakar

2013-03-19 04:46:16 8 Comments

... pivot (sum(A) for B in (X))

Now B is of datatype varchar2 and X is a string of varchar2 values separated by commas.
Values for X are select distinct values from a column(say CL) of same table. This way pivot query was working.

But the problem is that whenever there is a new value in column CL I have to manually add that to the string X.

I tried replacing X with select distinct values from CL. But query is not running.
The reason I felt was due to the fact that for replacing X we need values separated by commas.
Then i created a function to return exact output to match with string X. But query still doesn't run.
The error messages shown are like "missing righr parantheses", "end of file communication channel" etc etc.
I tried pivot xml instead of just pivot, the query runs but gives vlaues like oraxxx etc which are no values at all.

Maybe I am not using it properly.
Can you tell me some method to create a pivot with dynamic values?


@Rana Suhaib 2018-01-24 13:35:29


Test code is below

--  DDL for Table TMP_TEST

   (    "NAME" VARCHAR2(20), 
    "APP" VARCHAR2(20)
Insert into TMP_TEST (NAME,APP) values ('suhaib','2');
Insert into TMP_TEST (NAME,APP) values ('suhaib','1');
Insert into TMP_TEST (NAME,APP) values ('shahzad','3');
Insert into TMP_TEST (NAME,APP) values ('shahzad','2');
Insert into TMP_TEST (NAME,APP) values ('shahzad','5');
Insert into TMP_TEST (NAME,APP) values ('tariq','1');
Insert into TMP_TEST (NAME,APP) values ('tariq','2');
Insert into TMP_TEST (NAME,APP) values ('tariq','6');
Insert into TMP_TEST (NAME,APP) values ('tariq','4');
   (    "APP" VARCHAR2(20)

Insert into TMP_TESTAPP (APP) values ('1');
Insert into TMP_TESTAPP (APP) values ('2');
Insert into TMP_TESTAPP (APP) values ('3');
Insert into TMP_TESTAPP (APP) values ('4');
Insert into TMP_TESTAPP (APP) values ('5');
Insert into TMP_TESTAPP (APP) values ('6');
create or replace PROCEDURE temp_test(
  pcursor out sys_refcursor,
    PRESULT                   OUT VARCHAR2
 PRESULT := 'Nothing';

-- concating activities name using comma, replace "'" with "''" because we will use it in dynamic query so "'" can effect query.
         LISTAGG('''' || REPLACE(APP,'''','''''') || '''',',')
         WITHIN GROUP (ORDER BY APP) AS temp_in_statement 
            FROM TMP_TESTAPP);

-- designing dynamic query  

  V_QUERY := 'select * 
                from (  select NAME,APP 
                          from TMP_TEST   )   
               pivot (count(*) for APP in 
                     (' ||V_VALUES|| '))  
           order  by NAME' ;


 PRESULT := 'Success';

 PRESULT := SQLcode || ' - ' || SQLERRM;
END temp_test;

@Sarath Avanavu 2017-04-23 09:45:14

I am not exactly going to give answer for the question OP has asked, instead I will be just describing how dynamic pivot can be done.

Here we have to use dynamic sql, by initially retrieving the column values into a variable and passing the variable inside dynamic sql.


Consider we have a table like below.

enter image description here

If we need to show the values in the column YR as column names and the values in those columns from QTY, then we can use the below code.

  sqlqry clob;
  cols clob;
  select listagg('''' || YR || ''' as "' || YR || '"', ',') within group (order by YR)
  into   cols
  from   (select distinct YR from EMPLOYEE);

  sqlqry :=
  select * from
      select *
      from EMPLOYEE
    MIN(QTY) for YR in (' || cols  || ')

  execute immediate sqlqry;


enter image description here

If required, you can also create a temp table and do a select query in that temp table to see the results. Its simple, just add the CREATE TABLE TABLENAME AS in the above code.

sqlqry :=
  select * from

@अक्षय परूळेकर 2019-08-01 08:15:28

Error report - ORA-06550: line 5, column 74: PL/SQL: ORA-00923: FROM keyword not found where expected ORA-06550: line 5, column 3: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

@user7144213 2016-11-11 02:39:21

I used the above method (Anton PL/SQL custom function pivot()) and it done the job! As I am not a professional Oracle developer, these are simple steps I've done:

1) Download the zip package to find pivotFun.sql in there. 2) Run once the pivotFun.sql to create a new function 3) Use the function in normal SQL.

Just be careful with dynamic columns names. In my environment I found that column name is limited with 30 characters and cannot contain a single quote in it. So, my query is now something like this:

                SELECT DISTINCT
                    REPLACE(substr(T.UDF_TYPE_LABEL, 1, 30), '''''''','','') as Attribute,
                      WHEN V.udf_text is null     and V.udf_date is null and      V.udf_number is NOT null  THEN to_char(V.udf_number)
                      WHEN V.udf_text is null     and V.udf_date is NOT null and  V.udf_number is null      THEN to_char(V.udf_date)
                      WHEN V.udf_text is NOT null and V.udf_date is null and      V.udf_number is null      THEN V.udf_text
                      ELSE NULL END
                    AS VALUE
                    project   P
                LEFT JOIN UDFVALUE V ON P.proj_id     = V.proj_id 
                    P.delete_session_id  IS NULL AND
                    T.TABLE_NAME = ''PROJECT''

Works well with up to 1m records.

@Scott 2015-07-15 02:27:54

For later readers, here is another solution

allowing a query like

select * from table( pivot(  'select deptno,  job, count(*) c from scott.emp group by deptno,job' ) )

@user3141985 2018-12-10 06:11:31

Does this solution works in Oracle 11g? As I am unable to execute subquery in this format.

@Scott 2018-12-10 08:06:30

Given the dates involved, then yes, it should. 12c wasn't around back then. Double check privileges.

@user2179919 2014-05-16 15:16:49

You cannot put a dynamic statement in the PIVOT's IN statement without using PIVOT XML, which outputs some less than desirable output. However, you can create an IN string and input it into your statement.

First, here is my sample table;

  myNumber    myValue myLetter
---------- ---------- --------
         1          2 A        
         1          4 B        
         2          6 C        
         2          8 A        
         2         10 B        
         3         12 C        
         3         14 A      

First setup the string to use in your IN statement. Here you are putting the string into "str_in_statement". We are using COLUMN NEW_VALUE and LISTAGG to setup the string.

clear columns
COLUMN temp_in_statement new_value str_in_statement
    LISTAGG('''' || myLetter || ''' AS ' || myLetter,',')
        WITHIN GROUP (ORDER BY myLetter) AS temp_in_statement 
    FROM (SELECT DISTINCT myLetter FROM myTable);

Your string will look like:

'A' AS A,'B' AS B,'C' AS C

Now use the String statement in your PIVOT query.

    (SELECT myNumber, myLetter, myValue FROM myTable)
    PIVOT (Sum(myValue) AS val FOR myLetter IN (&str_in_statement));

Here is the Output:

  MYNUMBER      A_VAL      B_VAL      C_VAL
---------- ---------- ---------- ----------
         1          2          4            
         2          8         10          6 
         3         14                    12 

There are limitations though. You can only concatenate a string up to 4000 bytes.

@lourdh 2016-04-22 05:50:03

while trying this i am getting below oracle error: ORA-56900: bind variable is not supported inside pivot|unpivot operation

@Ram 2017-02-02 16:43:44

How to implement this in Oracle procedure? pls give an example

@MattSom 2019-04-01 11:20:37

What is the clear columns command for?

@Hamlet Mendez 2019-07-26 20:59:26

how can I put the first select query inside the IN clause without a variable?

@A.B.Cade 2013-03-19 05:49:01

You can't put a non constant string in the IN clause of the pivot clause.
You can use Pivot XML for that.

From documentation:

subquery A subquery is used only in conjunction with the XML keyword. When you specify a subquery, all values found by the subquery are used for pivoting

It should look like this:

select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in(any)
) t;

You can also have a subquery instead of the ANY keyword:

select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in (select cl from t_bb)
) t;

Here is a sqlfiddle demo

@prabhakar 2013-03-19 07:45:54

hi your method is working actually but output i am getting in xml format. can i get output as a table with rows and columns ?

@A.B.Cade 2013-03-19 08:09:54

AFAIK, not dynamically... But how do you intend to use a result which you don't know its structure ?

Related Questions

Sponsored Content

16 Answered Questions

[SOLVED] How do I limit the number of rows returned by an Oracle query after ordering?

7 Answered Questions

40 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

45 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

21 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2075601 View
  • 1047 Score
  • 21 Answer
  • Tags:   sql oracle

37 Answered Questions

28 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2531083 View
  • 1710 Score
  • 28 Answer
  • Tags:   sql duplicates

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

Sponsored Content