Oracle SQL - 将行动态转换为列 [英] Oracle SQL - Converting rows into columns dynamically

问题描述

我有一个包含 150 多个数据的表(能力)，用户可以随时插入或更新这些数据.我想将此表的行转换为列并创建另一个视图.此视图应将行作为员工，其中员工表是单独定义的.我尝试使用 PIVOT 函数，但无法解决如何定义列，因为它是动态的.

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.

原表

<头>

competency_id	competency_group	组织	位置	工作
英文	语言	my_org	my_pos	my_Job
法语	语言	my_org	my_pos	my_Job
日语	语言	my_org	my_pos	my_Job

新视图(预期)

<头>

英文	法语	日语
-------	------	--------
-------	------	--------
-------	------	--------

编辑当我像下面这样尝试时，它适用于指定的列

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')
)

但是当我尝试使用如下所示的 select 语句时，它会出现错误

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)
)

推荐答案

我知道您没有使用 MSSQL，但是这个概念可能会对您有所帮助.

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

我不确定，但您可能需要 LISTAGG 来代替 STRING_AGG.我只是想在这里传达这个概念.

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 - 将行动态转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！