小组行值进入列，而无需硬编码PL SQL

Question

我有一张看起来像这样的桌子：

月份	名称
七月的	艾莉·
七月七月	肯
·肯	·
莱伊·游行	·
艾莉·李·克拉姆	弗罗伊
弗罗伊·	丹妮丝·
阿普尔	，

我希望它看起来像这样：

7	月	3月
·克拉姆·恩·弗罗伊·肯·丹妮丝（ Ally	Lee	Kram
don	Froyo
Ken	Denise）

这是我尝试过的解决方案：

SELECT
(SELECT name FROM mytable WHERE month='July') AS July,
(SELECT name FROM mytable WHERE month='March') AS March,
(SELECT name FROM mytable WHERE month='April') AS April
FROM DUAL; 

但是，我不想像month ='七月'那样进行编码的一个月值

吗？

Answer 1

似乎您正在尝试通过有条件的汇总来构建查询，并在一年之内按照其顺序排序

SELECT MAX(CASE
             WHEN month = 'March' THEN
              name
           END) AS March,
       MAX(CASE
             WHEN month = 'April' THEN
              name
           END) AS April,
       MAX(CASE
             WHEN month = 'July' THEN
              name
           END) AS July
  FROM (SELECT t.*,
               ROW_NUMBER() OVER(PARTITION BY month ORDER BY name) AS rn
          FROM t) -- "t" represents your mentioned table
 GROUP BY rn
 ORDER BY rn

，并且您可以根据希望使其动态化生成存储的功能代码，例如

CREATE OR REPLACE FUNCTION Get_People_By_Months RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_cols      VARCHAR2(32767);
BEGIN
  SELECT LISTAGG('MAX(CASE WHEN month = '''||month||''' THEN name END) AS '||month ,',') 
          WITHIN GROUP (ORDER BY month_nr)        
    INTO v_cols
    FROM (SELECT DISTINCT t.month, m.month_nr
            FROM t
            JOIN (SELECT TO_CHAR(TO_DATE(level, 'mm'),
                                 'Month',
                                 'NLS_DATE_LANGUAGE=English') AS month, 
                         level AS month_nr
                    FROM dual
                  CONNECT BY level <= 12) m
              ON t.month = TRIM(m.month));

  v_sql :='SELECT '|| v_cols ||'
             FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY month ORDER BY name) AS rn 
                     FROM t)
            GROUP BY rn 
            ORDER BY rn';


  OPEN v_recordset FOR v_sql;
  DBMS_OUTPUT.PUT_LINE(v_sql);
  RETURN v_recordset;
END;
/

从中调用SQL Developer 的控制台

SQL> DECLARE
    result SYS_REFCURSOR;
BEGIN
   :result := Get_People_By_Months;
END;
/

SQL> PRINT result;