Oracle SQL - Converting rows into columns dynamically

Question

I have a table(competency) with 150+ data which can be inserted or updated by user at anytime. I want to convert the rows of this table to columns and create another view. This view should have rows as employees where the employee table is defined separately. I tried to use PIVOT function but couldn't resolve how to define the columns as it will be dynamic.

orginal table

competency_id	competency_group	organization	position	job
ENGLISH	LANGUAGE	my_org	my_pos	my_Job
FRENCH	LANGUAGE	my_org	my_pos	my_Job
JAPANESE	LANGUAGE	my_org	my_pos	my_Job

new view (expected one)

ENGLISH	FRENCH	JAPANESE
-------	------	--------
-------	------	--------
-------	------	--------

Edit when I tried it like below it worked for specified columns

select * from (
   select competency_id
   from competency_tab t
)
pivot 
(
   count(competency_id)
   for competency_id in ('ENGLISH', 'GERMAN')
)

But when I tried to uses select statement like below it gives an error

select * from (
   select competency_id
   from competency_tab t
)
pivot 
(
   count(competency_id)
   for competency_id in (SELECT DISTINCT competency_id FROM competency_tab)
)

I recall having done similar in MSSQL and that was accomplished by dynamically building the SQL string and executing that with the `EXEC` command. The dynamic query used the `PIVOT` command. Since that was done in MSSQL it may not help, but you'll probably have to create the SQL dynamically. — Michael Z., May 18 '21 at 06:04
If you review how PIVOT works and can make it work with a sample table then you will know what you need to do when building the SQL string. Basically you need a query that can get you the distinct of your `competency_id` so you can inject that into the Pivot SQL string. — Michael Z., May 18 '21 at 06:08
@MichaelZ. I edited the question, if you have any idea on that — jayz, May 18 '21 at 06:15
You nailed it! You first SQL works. You need to do that distinct query to build that list in the `for` line. It needs to be one in a `nvarchar` variable and executed using `EXEC @myVar` — Michael Z., May 18 '21 at 06:19
Your would end up with something like this `DECLARE @SQL NVARCHAR(MAX) = 'select * from (select competency_id from competency_tab t) pivot (count(competency_id) for competency_id in ('ENGLISH', 'GERMAN'))'` then just run it `EXEC @SQL` — Michael Z., May 18 '21 at 06:31
I am actually using plsql and want to create a view out of this. I'm sorry I tried but seems like this method wont work. please suggest if you have any idea — jayz, May 18 '21 at 06:37
it would need to be a sproc, but it would work like I describe — Michael Z., May 18 '21 at 06:39
Does this answer your question? [Dynamic pivot in oracle sql](https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql) It seems this question is asked every day. — William Robertson, May 18 '21 at 06:55

Michael Z. · Answer 1 · 2021-05-18T06:58:17.520

I know you are not using MSSQL, however this concept might help you out.

I'm not sure but in place of STRING_AGG you might need LISTAGG. I'm just trying to convey the concept here.

CREATE PROCEDURE PivotMyTable
AS

BEGIN
  DECLARE @cols NVARCHAR(MAX) = '';
  DECLARE @sql NVARCHAR(MAX) = 'select * from (select competency_id from competency_tab t) pivot (count(competency_id) for competency_id in (##COLUMS##))'

  WITH T
  AS
  (SELECT DISTINCT
      competency_id
    FROM competency_tab)
  SELECT
    @cols = STRING_AGG(QUOTENAME(T.competency_id, ''''), ',')
  FROM T

  SET @sql = REPLACE(@sql, '##COLUMNS##', @cols);

  EXEC @sql;

END

Oracle SQL - Converting rows into columns dynamically

1 Answers1

Linked