根据另一列的值在单元格中显示LISTAGG值 [英] Displaying LISTAGG values in a cell based on the value of another column

问题描述

我正在完成一项任务，但是我已经接近完成了，但是一段时间以来，我一直在努力解决其中的一小部分.我需要显示一个具有SCHEDULE列和DAYS列的表，其中DAYS列显示基于SCHEDULE的天数列表.时间表只有两个选项:周末"和工作日".

I am working on an assignment and I'm very close to finishing however, I have been struggling with one bit of it for a while. I need to display a table that has a SCHEDULE column, and a DAYS column where the DAYS column shows a list of days based on the SCHEDULE. There are only two options for SCHEDULE: 'Weekend' and 'Weekday.'

这是我想要达到的最终结果:

Here is the end result that I am trying to achieve:

ID   Schedule     Days
001  Weekend      Saturday, Sunday

我已经创建了一个流程，并且正在使用以下代码:

I have created a process and am using this code:

BEGIN
UPDATE schedules
SET days =
        WHEN schedule = 'Weekend' THEN
        (SELECT LISTAGG(day, ', ') WITHIN GROUP (ORDER BY day_order)
        FROM days
        WHERE schedule = 'Weekend'
        )
        WHEN schedule = 'Weekday' THEN
        (SELECT LISTAGG(day, ', ') WITHIN GROUP (ORDER BY day_order)
        FROM days
        WHERE schedule = 'Weekday'
        )
    END

执行此操作时，出现错误:

When I do this, I get the error:

在遇到以下情况之一时遇到符号文件结束":begin function pragma procedure

Encountered the symbol "end-of-file" when expecting one of the following: begin function pragma procedure

如果有人可以帮我，将不胜感激！

If anyone can give me a hand, it would be much appreciated!

推荐答案

您的更新与此等效.

UPDATE schedules s 
SET    days = (SELECT LISTAGG(day, ', ') 
                        within group ( ORDER BY day_order ) 
               FROM   days d 
               WHERE  d.schedule IN ( 'Weekend', 'Weekday' ) 
                      AND d.schedule = s.schedule 
               GROUP  BY d.schedule ) ;

但是，我会不推荐.将记录存储为逗号分隔的值势必会在将来给您带来麻烦.仅使用它来显示查询结果，如下所示.假设在您的天数表中每天都有唯一的行，如果有重复行，则将其join转换为与天数不同的结果集.

But, I would not recommend this. Storing the records as comma separated values is bound to cause problems for you in future. Only use it to display the results using a query like below. This assumes that in your days table, there are unique rows for each day.If there are duplicates, join it to the distinct result set from days.

SELECT d.id, 
       d.schedule, 
       LISTAGG(s.day, ', ') 
         within GROUP ( ORDER BY d.day_order ) 
FROM   days d 
       join schedules s 
         ON ( d.schedule = s.schedule ) 
WHERE  d.schedule IN ( 'Weekend', 'Weekday' ) 
GROUP  BY d.id, 
          d.schedule 

这篇关于根据另一列的值在单元格中显示LISTAGG值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！