pivot report with dynamic names

Balaji_Madhavan

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

Frank Kulash

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.