Forum Stats

  • 3,750,344 Users
  • 2,250,158 Discussions
  • 7,866,941 Comments

Discussions

pivot report with dynamic names

Balaji_Madhavan
Balaji_Madhavan Member Posts: 8 Green Ribbon

Hi Team,

I need a pivot kind of report in which i cannot use the employee name hardcoded, below is my query pls help me out

/* Code to execute my query */

create table questions (emp_name varchar2(100),question varchar2(1000),answer varchar2(1000));

Insert into questions values('emp1','how old are you','27');

Insert into questions values('emp2','how old are you','28');

Insert into questions values('emp3','how old are you','29');

Insert into questions values('emp1','what is your fav color','blue');

Insert into questions values('emp2','what is your fav color','black');

Insert into questions values('emp3','what is your fav color','red');

/* end */

SELECT  *

FROM   questions

PIVOT   ( MIN (answer)

          FOR emp_name IN ('emp1','emp2'));

Here i dont want to hardcode emp1 and emp2 it need to be generated dynamically, I have tried using sub query in IN condition but it dint work . My output should be like the below image

we have so many employees this is a sample data

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,887 Red Diamond

    Hi,

    That's a display problem. Display problems are best solved in the front end. Report writers often have tools for doing what you want.

    If your front end can't do it, then you need Dynamic SQL. The number of columns in a SQL result set, and the name of each column, must be hard-coded in the query. To do anything else requires Dynamic SQL.

    Here's one way to do it in SQL*Plus:

    -- Preliminary Query
    COLUMN emp_name_list_col NEW_VALUE emp_name_list
    
    WITH  distinct_emp_names  AS
    (
      SELECT  emp_name
      FROM   questions
      WHERE   emp_name <> 'emp3'
      GROUP BY emp_name
    -- HAVING  ...  -- If wanted
    )
    SELECT  LISTAGG ('''' || emp_name || ''''
        	  	 , ','
    		 )
    		 WITHIN GROUP (ORDER BY emp_name)
    		  AS emp_name_list_col
    FROM   distinct_emp_names
    ;
    
    -- Main Query
    SELECT *
    FROM  questions
    PIVOT  ( MIN (answer)
         FOR emp_name IN (&emp_name_list));
    

    Notice how similar the main query is to the query you posted.

    Output:

    Why don't you want a column for 'emp3'? You can use any combination of conditions in WHERE and .clauses of distnct_emp_naames to get the emp_names you want. If you omit both the WHERE and HAVING clauses, you'll get all the emp_names.