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:
I would like the response fields that go with ('Restraint?','History','Findings','Treatment') to have their own column for each DLSEQUENCE field.