I need some help and I am not even quite sure if this is doable, but you can tell me for sure.
I have a field named ACTIVITIES ruled by a listagg. However, the user would like to have the output of this list divided into two different fields.
- Right now, the Listagg that I have lists all activities for College and High school alike under the same field (ACTIVITIES) and in one record per name. That way it shows in where specific activities alumni were enrolled in when they were at College and High School. For example, this is how it looks now:
John Doe Football (CO), Swimming (HS), Theater (CO)...
Jane Doe Cross Country (HS), Dance (CO), Sheaf(CO), Tennis (HS)...
HS stands for High School
CO stands for College
- What I would like to do then, if possible, is to have the following fields (US_Activities and CO_Activities) instead of the current ACTIVITIES field:
Name US_Activities CO_Activities
John Doe Swimming (HS)... Football (CO), Theater (CO)...
Jane Doe Cross Country (HS), Tennis (HS)... Dance (CO), Sheaf(CO)...
-As of now, this is my line of code for that particular field:
LISTAGG(STVACTC_DESC, ',') WITHIN GROUP (ORDER BY APRACTY_PIDM) AS ACTIVITIES
stands for the activities' description. In other words the ones listed.
is the ID we pull to display all the names. In other words the unique ID for this table.
Now, what I was trying to do was to make a CASE statement in which by selecting only the High School year and the code for High school activities it would give me a list off only High School activities for those unique IDs that were enrolled in an activity in high school.
This is my failure example:
CASE WHEN PREF_SCHOOL = 'HS" AND ACTC_CODE LIKE 'H%'
THEN LISTAGG(STVACTC_DESC, ',') WITHIN GROUP (ORDER BY APRACTY_PIDM) AS HS_ACTIVITIES
but as you have already figured out, it did'nt work.
Do you have any recommendation or ideas that can help.
is the code for alumni that stands for either High School or College attendance.
stands for the codes of activities; those starting with H are for high school; those starting with C are for college, and some other letters for some other activities I do not need for now.
I desperately need your help and expertise.
Thanks a million!