Oracle SQL - Converting Rows Into Columns Dynamically

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 vie

Solution 1:

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