将n行分组到列中- oracle

Question

我遇到了一种情况，我需要将'n‘行分成列组。例如，下面是dataset

COMMENT_TEXT  
  T1
  T2
  T3
  T4
  T5
  T6

预期产出：

  SUN  MON  TUE
  T1   T2   T3
  T4   T5   T6

我的问题是：

SELECT htbp1.comment_text 
FROM hxc_time_building_blocks htbp,
     hxc_time_building_blocks htbp1 
  WHERE     htbp1.parent_building_block_id = htbp.time_building_block_id
     AND htbp1.parent_building_block_ovn = htbp.parent_building_block_ovn
     AND htbp.parent_building_block_id = 116166
     AND htbp.parent_building_block_ovn = 1      
ORDER BY htbp1.time_building_block_id 

有什么方法可以用'n‘行和没有聚合函数来做枢轴吗？

编辑: T1/T2/T3作为样本数据集，但在实际中它可以是任意随机的自由文本或null。

SELECT * FROM (SELECT htbp1.comment_text, TO_CHAR (htbp.start_time, 'DY') par_time,
trunc((rownum-1) / 7) buck
FROM hxc_time_building_blocks htbp,
 hxc_time_building_blocks htbp1,
 hxc_timecard_summary hts 
 WHERE     hts.RESOURCE_ID = :p_resource_id
                 AND TRUNC(hts.STOP_TIME) = TRUNC(:p_wkend_date)
  AND htbp1.parent_building_block_id = htbp.time_building_block_id
 AND htbp1.parent_building_block_ovn = htbp.parent_building_block_ovn
 AND htbp.parent_building_block_id = hts.timecard_id
 AND htbp.parent_building_block_ovn = hts.timecard_ovn      
ORDER BY htbp1.time_building_block_id ) PIVOT( max(comment_text)   FOR par_time
                                                   IN  ('SUN' AS "SUN",
                                                       'MON' AS "MON",
                                                       'TUE' AS "TUE",
                                                       'WED' AS "WED",
                                                       'THU' AS "THU",
                                                       'FRI' AS "FRI",
                                                       'SAT' AS "SAT"));

当我添加另一个表'hxc_timecard_summary‘(它是父表)时，数据就会变得疯狂，但是如果我使用硬编码参数(如第一个表中的参数)，那么行就会显示得很好。

Answer 1

PIVOT也使用聚合函数，但不需要通过以下方法进行分组：

with tab as (
  select sysdate - 7 date_col, 'T1' comment_text from dual
  union all select sysdate - 6, 'T2' from dual
  union all select sysdate - 5, 'T3' from dual
  union all select sysdate - 4, 'T4' from dual
  union all select sysdate - 3, 'T5' from dual
  union all select sysdate - 2, 'T6' from dual
  union all select sysdate - 1, 'T7' from dual
)
select * from (select to_char(date_col, 'D') day_of_week, comment_text from tab)
PIVOT (max(comment_text) for day_of_week in (7 as sun, 1 as mon, 2 as tue));

此外，我认为您需要第二列和日期组成您的新列。

而且不能使用表达式for子句--这应该是列。例如，这是行不通的：

select * from tab
PIVOT (max(comment_text) for to_char(date_col, 'D') in (7 as sun, 1 as mon, 2 as tue));

因为to_char(date_col, 'D')

Answer 2

尝试使用枢轴。它允许将行映射到列。我想是从11克开始的。

Answer 3

with tab as (
  select 'T1' comment_text from dual
  union all select 'T2' from dual
  union all select 'T3' from dual
  union all select 'T4' from dual
  union all select 'T5' from dual
  union all select 'T6' from dual
)
select regexp_substr(txt, '[^,]+', 1, 1) sun, 
       regexp_substr(txt, '[^,]+', 1, 2) mon, 
       regexp_substr(txt, '[^,]+', 1, 3) tue
from (
  select buck, wm_concat(comment_text) txt
  from (
    select comment_text, trunc((rownum-1) / 3) buck 
    from (select comment_text from tab order by comment_text)
  )    
  group by buck
);

wm_concat(comment_text) (Oracle10g)=

listagg(comment_text, ',') within group(order by comment_text) (Oracle11g)

但这两个函数都是聚合的。