Solved

Narrowing down the output of LISTAGG

Posted on 2014-01-20
13
1,717 Views
Last Modified: 2014-01-21
Hi Experts!

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:

Name              Activities
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

Open in new window


STVACTC_DESC stands for the activities' description. In other words the ones listed.
APRACTY_PIDM 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

Open in new window


but as you have already figured out, it did'nt work.
Do you have any recommendation or ideas that can help.

PREF_SCHOOL is the code for alumni that stands for either High School or College attendance.

ACTC_CODE 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!
0
Comment
Question by:panterall
  • 5
  • 5
  • 2
13 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Please provide sample source data (as text, not screen captures please)  along with expected results
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 300 total points
Comment Utility
as LISTAGG ignores null values, this approach should do:
SELECT ...
 , LISTAGG( CASE WHEN PREF_SCHOOL = 'HS' THEN STVACTC_DESC END, ',') WITHIN GROUP (ORDER BY APRACTY_PIDM)  HS_ACTIVITIES
 , LISTAGG( CASE WHEN PREF_SCHOOL = 'CO' THEN STVACTC_DESC END, ',') WITHIN GROUP (ORDER BY APRACTY_PIDM)  CO_ACTIVITIES

Open in new window

0
 
LVL 73

Accepted Solution

by:
sdstuber earned 200 total points
Comment Utility
I think your sample data would look something like this...

Name        Activities         Actc_Code
------------------------------------------
John Doe    Football (CO)      C
John Doe    Theater (CO)       C
John Doe    Swimming (HS)      H
Jane Doe    Cross Country (HS) H
Jane Doe    Tennis (HS)        H
Jane Doe    Dance (CO)         C
Jane Doe    Sheaf (CO)         C

Open in new window


If so, then try this...


  SELECT name,
         LISTAGG(CASE WHEN actc_code = 'H' THEN activities END, ',')
             WITHIN GROUP (ORDER BY apracty_pidm)
             hs_activities,
         LISTAGG(CASE WHEN actc_code = 'C' THEN activities END, ',')
             WITHIN GROUP (ORDER BY apracty_pidm)
             co_activities
    FROM yourtable
   WHERE actc_code IN ('C', 'H')
GROUP BY name


If your data source is something else, please post in similar format
0
 
LVL 1

Author Comment

by:panterall
Comment Utility
Hi!

Thank you very very much Guy and sdstuber for your assistance and expertise.
I have tried both options and this is what I got:

1) Guy, I used the piece of code you kindly share with me and it works perfectly in the sense that as long as it is only HS it separates those from College. However, when it is the case for CO, everything else shows up altogether (HS and CO) for every record. I am not sure why is this happening , but my guess is that the table somehow has something to do with it. But definitely what I like is that keeps al concatenated in one record per person.

This is my piece of code based on yours:

LISTAGG(CASE WHEN PREF_SCHOOL = 'HS' THEN STVACTC_DESC END, ',')
               WITHIN GROUP (ORDER BY APRACTY_PIDM)                  AS HS_ACTIVITIES

The results of this piece of code are something like this, and I ran it with the criteria of 'CO' alumni:

NAME                   HS_Activities                  CO_ACTIVITIES
John Doe               Null                                   Dance (CO), Rugby(CO), Theater(CO)
Jane Doe               Null                                   Chorus (CO), Solar Car (CO)
Jack Frost              Swimming(HS)                 Newspaper (CO), Swimming (HS), Volleyball(CO)
Jordan Michael    Football(HS), Bball(HS)    Footbal(HS), Cross-Country(CO), Bball(HS), Rugby


2) sdstuber, I also tried your code, and it works perfectly since in your code you are including the ACTC_CODE (activity code) instead of the PREF_SCHOOL. What this does is that this separates HS activities from those of CO. However, it is not listing the activities per  record per name/ID. Therefore it is creating a record per each activity per person, as if the LISTAGG function were not there.

This is the piece of code I used based on yours:
LISTAGG(CASE WHEN APRACTY_ACTC_CODE LIKE 'H%' THEN STVACTC_DESC END, ',')
                          WITHIN GROUP (ORDER BY APRACTY_PIDM)        AS HS_ACTIVITIES

And these are the results:

NAME                   HS_Activities                  CO_ACTIVITIES
John Doe               Null                                   Dance (CO)
John Doe               Null                                   Rugby(CO)
John Doe               Null                                   Theater(CO)
Jane Doe               Null                                   Chorus (CO)
Jane Doe               Null                                   Solar Car (CO)
Jack Frost              Swimming(HS)                 Null
Jack Frost              Null                                   Newspaper (CO)
Jack Frost              Null                                   Volleyball(CO)
Jordan Michael     Football(HS)                     Null
Jordan Michael     Bball(HS)                          Null
Jordan Michael     Null                                  Cross-Country(CO)
Jordan Michael      Null                                  Rugby

I definitely would like to have something similar to the last results, but concatenated in one  record per person name/ID.

Do you have any suggestions?
I hope I am making sense

Thanx a million
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
please post sample data and expected results

merely describing the results without an accurate picture of inputs doesn't help.

post data that can be used to build a test case and you eliminate all the guess work on our part.

If you give me a test case, I won't post answer until I've tested it and shown it produces exactly what you asked for.

Without a test case our answers are just shots in the dark.
0
 
LVL 1

Author Comment

by:panterall
Comment Utility
This is my code:

SELECT NAME AS NAME
           , LISTAGG (CASE WHEN APRACTY_ACTC_CODE LIKE 'H%' THEN STVACTC_DESC END, ',')
                  WITHIN GROUP (ORDER BY APRACTY_PIDM)    AS HS_ACTIVITIES
           , LISTAGG (CASE WHEN APRACTY_ACTC_CODE LIKE 'C%' THEN STVACTC_DESC END, ',')
                  WITHIN GROUP (ORDER BY APRACTY_PIDM)    AS CO_ACTIVITIES
FROM AYVXACT
WHERE APRACTY_ACTC_CODE LIKE 'U%' OR APRACTY_ACTC_CODE LIKE 'C%'.

These are the results I am getting:

NAME                   HS_Activities                  CO_ACTIVITIES
John Doe               Null                                   Dance (CO)
John Doe               Null                                   Rugby(CO)
John Doe               Null                                   Theater(CO)
Jane Doe               Null                                   Chorus (CO)
Jane Doe               Null                                   Solar Car (CO)
Jack Frost              Swimming(HS)                 Null
Jack Frost              Null                                   Newspaper (CO)
Jack Frost              Null                                   Volleyball(CO)
Jordan Michael     Football(HS)                     Null
Jordan Michael     Bball(HS)                          Null
Jordan Michael     Null                                  Cross-Country(CO)
Jordan Michael      Null                                  Rugby

These are the results I would like to get:

NAME                   HS_Activities                       CO_ACTIVITIES
John Doe                Null                                      Dance (CO), Rugby(CO), Theater(CO)
Jane Doe                Null                                      Chorus (CO), Solar Car (CO)
Jack Frost              Swimming(HS)                   Newspaper (CO), Volleyball(CO)
Jordan Michael    Football(HS), Bball(HS)     Cross-Country(CO), Rugby
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
again, showing the output without the correspoinding input isn't very helpful.

what is the input data?  give me something that I can load into my system to test with
0
 
LVL 1

Author Comment

by:panterall
Comment Utility
Got you! I apologize I didn't quite understand at first.

The input data is a graduation year, '2010' for example; and a type of school, either College or High School ('CO' or 'HS). This displays a list of people' information that attended either college or High School in that year. For those purposes I am going to place a more extended version of this code. I have bolded the parts of the code that are related to this case and towards the end I, before the Group By statement, I have underlined the part where you can say if you want it to be College or High School and what year.

here it is (i hope this is more useful):

SELECT AYVNAME_PIDM                                                                           AS PIDM
          , AYVNAME_LAST_NAME                                                                 AS LAST_NAME
          , AYVNAME_FIRST_NAME                                                                AS FIRST_NAME
          , CASE WHEN AYVNAME_MI_NAME IS NOT NULL THEN AYVNAME_MI_NAME || ' '
                 ELSE '' END                                                                                         AS MIDDLE_NAME
          , AYVNAME_PREFIX                                                                                   AS PREFIX
          , AYVNAME_SUFFIX                                                                                   AS SUFFIX
          ,  'College ' || AYVINFO_PREF_CO_YEAR                                                 AS REPORT_TITLE
          , CASE WHEN AYVINFO_PREF_HS_SCHOOL = 'HS' THEN AYVINFO_PREF_HS_SCHOOL END          AS HS_ALUM
          , CASE WHEN AYVINFO_PREF_HS_SCHOOL = 'HS' THEN AYVINFO_PREF_HS_YEAR END            AS HS_YEAR
          , AYVINFO_PREF_CO_SCHOOL                                                           AS CO_ALUM
          , AYVINFO_PREF_CO_YEAR                                                                 AS CO_YEAR
          , AYVALUM_GRADUATE_TYPE_IND                                                AS GRADUATE_IND
          , DEGREES                                                                                       AS DEGREES
          , LISTAGG(CASE WHEN APRACTY_ACTC_CODE LIKE 'U%' THEN STVACTC_DESC END, ',') WITHIN GROUP (ORDER BY APRACTY_PIDM)         AS HS_ACTIVITIES
          ,  , LISTAGG(CASE WHEN APRACTY_ACTC_CODE LIKE 'C%' THEN STVACTC_DESC END, ',') WITHIN GROUP (ORDER BY APRACTY_PIDM)         AS CO_ACTIVITIES

          , AYVINFO_ADDR_LINE_1                                                              AS ADDR_LINE_1
          , AYVINFO_ADDR_LINE_2                                                              AS ADDR_LINE_2
          , AYVINFO_ADDR_LINE_3                                                              AS ADDR_LINE_3
          , AYVINFO_CITY || CASE WHEN AYVINFO_ST_PROV IS NOT NULL
                                         THEN ', ' || AYVINFO_ST_PROV
                                         ELSE '  ' END || AYVINFO_ZIP                        AS CSZ
          , AYVINFO_CITY                                                                     AS CITY
          , AYVINFO_ST_PROV                                                                  AS STATE
          , AYVINFO_ZIP                                                                      AS ZIP
          , AYVINFO_NATION                                                                   AS NATION
          , AYVINFO_HOME_TELE_NUMBER                                                         AS HOME_PHONE
          , AYVINFO_WORK_TELE_NUMBER                                                         AS WORK_PHONE
          , AYVINFO_MOBILE_TELE_NUMBER                                                       AS MOBILE_PHONE
          , CASE WHEN AYVXMIE_EXCL_CODE = 'EDR'
                 THEN 'Do Not Email'
                 ELSE AYVINFO_EMAIL_ADDR END                                                 AS EMAIL
          , CASE WHEN AYVINFO_DECEASED_IND = 'Y' OR
                      AYVINFO_ADDR_TYPE IS NULL THEN '1' ELSE '' END                         AS CNT_LOST
          , CASE WHEN AYVINFO_DECEASED_IND = 'Y' THEN '(Deceased)' || ' ' ELSE '' END        AS Deceased_Ind
          , CASE WHEN AYVINFO_ADDR_TYPE IS NULL AND AYVINFO_DECEASED_IND IS NULL
                     THEN '(No Address Available)' ELSE '' END                               AS Inactive
          , MAX(CASE WHEN AYVXMIE_EXCL_CODE='NDO'  
                     THEN '(Do Not Contact)' || ' ' ELSE '' END)                             AS NDO_EXCL
          , MAX(CASE WHEN AYVXMIE_EXCL_CODE='NOS'
                     THEN '(No Solicitation)' || ' ' ELSE '' END)                            AS NOS_EXCL
          , MAX(CASE WHEN AYVXMIE_EXCL_CODE IN ('NOS','NPS')
                     THEN '(No Phone Solicitation)' || ' ' ELSE '' END)                      AS NPS_EXCL
          , MAX(CASE WHEN AYVXMIE_EXCL_CODE='NML'
                     THEN '(Do Not Mail)' || ' ' ELSE '' END)                                AS NML_EXCL
          , MAX(CASE WHEN AYVXMIE_EXCL_CODE IN ('EDR','ENM','NDO','EAD')
                     THEN '(Do Not Email)' || ' ' ELSE '' END)                               AS ENM_EXCL
       FROM AYVINFO PERS
         JOIN AYVNAME ON AYVINFO_PIDM = AYVNAME_PIDM
         LEFT JOIN (  SELECT DISTINCT AYVALLG_PIDM AS PIDM
                        FROM AYVALLG
                       WHERE AYVALLG_ANON_IND IS NOT NULL
                    ) ANON ON AYVINFO_PIDM = ANON.PIDM
        LEFT JOIN (SELECT DISTINCT APRACTY_PIDM,STVACTC_DESC, APRACTY_ACTC_CODE
                      FROM AYVXACT WHERE APRACTY_ACTC_CODE LIKE 'H%' OR APRACTY_ACTC_CODE LIKE 'C%' ) AYVXACT ON AYVINFO_PIDM = APRACTY_PIDM

         LEFT JOIN (SELECT DISTINCT PERSON_UID,DEGREES
                      FROM AAP_DEGREE) AAP_DEGREE ON AYVINFO_PIDM = PERSON_UID
         LEFT JOIN AYVALUM ON AYVINFO_PIDM = AYVALUM_PIDM
         LEFT JOIN AYVXMIE ON AYVINFO_PIDM = AYVXMIE_PIDM
      WHERE  (AYVINFO_PREF_CO_SCHOOL='CO' AND AYVINFO_PREF_CO_YEAR = '2010')
   GROUP BY AYVNAME_PIDM
          , AYVNAME_ID    
          , AYVNAME_NAME_LFMI    
          , AYVNAME_LAST_NAME    
          , AYVNAME_FIRST_NAME
          , AYVNAME_SALUTATION
          , AYVNAME_MI_NAME
          , AYVNAME_BIRTH_NAME
          , AYVNAME_PREFIX
          , AYVNAME_SUFFIX
          , AYVNAME_CM_NAME                                                            
          , AYVNAME_COUPLE_SALUTATION                                                        
          , AYVINFO_PREF_CO_SCHOOL
          , AYVINFO_PREF_CO_YEAR
          , AYVINFO_PREF_US_SCHOOL
          , AYVINFO_PREF_US_YEAR
          , AYVALUM_GRADUATE_TYPE_IND
          , DEGREES
          , APRACTY_PIDM
          , APRACTY_ACTC_CODE

          , AYVINFO_ADDR_LINE_1
          , AYVINFO_ADDR_LINE_2  
          , AYVINFO_ADDR_LINE_3  
          , AYVINFO_CITY    
          , AYVINFO_ST_PROV  
          , AYVINFO_ZIP
          , AYVINFO_NATION  
          , AYVINFO_HOME_TELE_NUMBER
          , AYVINFO_WORK_TELE_NUMBER
          , AYVINFO_MOBILE_TELE_NUMBER
          , AYVINFO_ADDR_TYPE
          , CASE WHEN AYVXMIE_EXCL_CODE = 'EDR'
                 THEN 'Do Not Email'
                 ELSE AYVINFO_EMAIL_ADDR END
          , AYVINFO_DECEASED_IND
  ORDER BY SORT_NAME
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 300 total points
Comment Utility
please remove
          , APRACTY_PIDM
          , APRACTY_ACTC_CODE

from the group by ...
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
no, I mean sample data,  not the query (although that might help too)
0
 
LVL 1

Author Comment

by:panterall
Comment Utility
Awesome Guy!!!!

That did it!! I can have now one field for CO-Activities and one for HS_Activities; and the best part is that those fields are listed(not creating multiple records per person) and do not contain CO activities in the HS activities field, and viceversa.

Thanks a lot to both!! This is such a simple yet elegant  (and functional) way to do it.
Can I just ask why by removing those two field from the GROUP BY statement changed the output?
0
 
LVL 1

Author Closing Comment

by:panterall
Comment Utility
I received great assistance and answers right away. And the best of all,  the expertise and answers of both experts shaped up the final solution that solved my issue. I got the solution just as I needed it
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Read about achieving the basic levels of HRIS security in the workplace.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now