Pivot without aggregate function

问题: I am trying to turn some parts of my rows into columns. To my knowledge, I am only able to use a pivot with an aggregate function,but I would just be pivoting text. For eac...

问题:

I am trying to turn some parts of my rows into columns. To my knowledge, I am only able to use a pivot with an aggregate function,but I would just be pivoting text. For each client I have up to 4 rows grouped by a DLSEQUENCE field. Instead of having the 4 rows, I would like everything to be on 1 row.

SELECT CASE
        WHEN Sched_time BETWEEN TRUNC(SCHED_TIME) + INTERVAL  '8' HOUR + INTERVAL '30' MINUTE
                         AND TRUNC(SCHED_TIME) + INTERVAL '14' HOUR + INTERVAL '45' MINUTE AND
          TO_CHAR(SCHED_TIME, 'DY') IN ('MON', 'TUE', 'WED', 'THU', 'FRI')
        THEN 'ABC' 
        ELSE 'DEF'
   END AS Organization,
   Client_Last_Name,
   Client_First_Name,
   Sched_Time,  
   Field_Name,
   CASE 
        WHEN Recoded_Response = '1' THEN 'Yes'
        WHEN Recoded_Response = '2' THEN 'No'
        ELSE Recoded_Response
    END AS Responses,
   Dlsequence
FROM DAILY_LOG_CUSTOM_DATA 
WHERE SERVICE_NAME = 'Medical'
AND FIELD_CATEGORY = 'Background Information'
AND Field_Name IN
(
'Restraint?',
'History',
'Findings',
'Treatment'
)
AND Sched_Time >= TO_DATE('2020-03-01 01:00:00', 'YYYY/MM/DD HH:MI:SS') 
AND Sched_Time  < TO_DATE('2020-03-31 12:59:00', 'YYYY/MM/DD HH:MI:SS')
Order BY Dlsequence

Here is my table:

enter image description here

I would like the response fields that go with ('Restraint?','History','Findings','Treatment') to have their own column for each DLSEQUENCE field.

enter image description here


回答1:

The following should do what you had in mind:

SELECT DLSEQUENCE,
       ORGANIZATION,
       CLIENT_LAST_NAME,
       CLIENT_FIRST_NAME,
       SCHED_TIME,
       LISTAGG("Restraint?", ',') WITHIN GROUP (ORDER BY DLSEQUENCE) AS "Restraint?",
       LISTAGG("Findings", ',') WITHIN GROUP (ORDER BY DLSEQUENCE) AS "Findings",
       LISTAGG("History", ',') WITHIN GROUP (ORDER BY DLSEQUENCE) AS "History",
       LISTAGG("Treatment", ',') WITHIN GROUP (ORDER BY DLSEQUENCE) AS "Treatment"
  FROM (SELECT DLSEQUENCE,
               ORGANIZATION,
               CLIENT_LAST_NAME,
               CLIENT_FIRST_NAME,
               SCHED_TIME,
               CASE
                 WHEN FIELD_NAME = 'Restraint?' THEN RESPONSES
                 ELSE NULL
               END AS "Restraint?",
               CASE
                 WHEN FIELD_NAME = 'Findings' THEN RESPONSES
                 ELSE NULL
               END AS "Findings",
               CASE
                 WHEN FIELD_NAME = 'History' THEN RESPONSES
                 ELSE NULL
               END AS "History",
               CASE
                 WHEN FIELD_NAME = 'Treatment' THEN RESPONSES
                 ELSE NULL
               END AS "Treatment"
          FROM YOUR_TABLE)
  GROUP BY DLSEQUENCE,
           ORGANIZATION,
           CLIENT_LAST_NAME,
           CLIENT_FIRST_NAME,
           SCHED_TIME

db<>fiddle here

  • 发表于 2020-06-27 19:14
  • 阅读 ( 94 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除