December 20, 2018 at 11:59 am
I am at a very basic/beginner level in SQL and currently working on generating a report for the agency I work for that shows contact info and demographic data for a people on our waiting list for services. The report is complete minus the phone numbers for contact. I am having trouble adding in those in. All of the phone numbers live in the same table and I have a successful query that groups all contact numbers in the table into a single column field by type. I am having trouble finding where and how to put this query it into my existing report. I am getting errors when trying to add this as a subquery into my select, and I even tried adding the numbers in as a pivot table with no luck. Pivot tables and subqueries are one part of SQL that I am still having a lot of trouble wrapping my head around and I am hoping someone here can help guide me with this problem of how to accomplish adding these phone numbers in.
Here is the phone number query I need to add into my report:
select person_id, listagg(phone_number||' ['||type||']', '; ')within group(order by type) as phone
from rpt_person_phone
group by person_id
And here is my report query:
SELECT DISTINCT
nvl("RPT_CLIENT"."CLIENT_NUMBER",cast(rpt_client.client_id as varchar(10))) as ClientID,
case
when "RPT_CLIENT"."GENDER_IDENTITY" is not null then "RPT_CLIENT"."GENDER_IDENTITY"
else "RPT_CLIENT"."GENDER"
end as ClientGender,
floor(months_between(current_date,rpt_client.birth_date)/12) as Age,
"RPT_CLIENT"."MAILING_ADDRESS_LINE_3" as Town,
"RPT_CLIENT_PROGRAMS"."PROGRAM" as Program,
"RPT_CLIENT_PAYER_PLAN"."PAYER_PLAN_NAME" as Payer,
case
when lower("RPT_CLIENT_MESSAGE_BOARD"."DIRECTIONS") like '%stuck%' then 'Yes'
else 'No'
end as Stuck,
case
when CF_LUK_PLACEMENT_REFERRAL_1370.INAPPROPRIATEBEH26 = 'Past' then 'Yes'
when CF_LUK_PLACEMENT_REFERRAL_1370.INAPPROPRIATEBEH26 = 'Present (w/ in last 12 months)' then 'Yes'
when CF_LUK_PLACEMENT_REFERRAL_1370.INAPPROPRIATEBEH26 is null then 'No'
else 'No'
end as Behaviors,
rpt_person_phone.phone_number as PhoneID,
rpt_person_phone.person_id as PersonID
FROM
"RPT_CLIENT"
INNER JOIN "RPT_CLIENT_PAYER_PLAN" ON
"RPT_CLIENT"."CLIENT_ID" = "RPT_CLIENT_PAYER_PLAN"."CLIENT_ID"
AND lower("RPT_CLIENT_PAYER_PLAN"."PAYER_PLAN_NAME") like '%dcf%'
INNER JOIN "RPT_CLIENT_PROGRAMS" ON
"RPT_CLIENT"."CLIENT_ID" = "RPT_CLIENT_PROGRAMS"."CLIENT_ID"
and "RPT_CLIENT_PROGRAMS"."BEGIN_DATE" is not null
and "RPT_CLIENT_PROGRAMS"."END_DATE" is null
AND ("RPT_CLIENT_PROGRAMS"."PROGRAM_ID" = 1047
OR "RPT_CLIENT_PROGRAMS"."PROGRAM_ID" = 1048
OR "RPT_CLIENT_PROGRAMS"."PROGRAM_ID" = 1062
OR "RPT_CLIENT_PROGRAMS"."PROGRAM_ID" = 1063
OR "RPT_CLIENT_PROGRAMS"."PROGRAM_ID" = 1064)
INNER JOIN "RPT_CLIENT_DOCUMENT" ON "RPT_CLIENT"."CLIENT_ID" = "RPT_CLIENT_DOCUMENT"."CLIENT_ID"
left join CF_LUK_PLACEMENT_REFERRAL_1370 on
RPT_CLIENT_document.DOCUMENT_ID = CF_LUK_PLACEMENT_REFERRAL_1370.DOCUMENT_ID
LEFT JOIN "RPT_CLIENT_MESSAGE_BOARD" ON
"RPT_CLIENT"."CLIENT_ID" = "RPT_CLIENT_MESSAGE_BOARD"."CLIENT_ID"
INNER JOIN "RPT_PERSON_PHONE" ON "RPT_CLIENT"."CLIENT_ID" = "RPT_PERSON_PHONE"."PERSON_ID"
December 20, 2018 at 12:48 pm
If you're using SQL Server then LISTAGG isn't going to work because that's an Oracle function.
If you're using SQL Server 2017 or Azure, I think you can use STRING_AGG which would look something like this:
SELECT person_id , STRING_AGG(phone_number, ',')
WITHIN GROUP (ORDER BY phone_number) AS phone_numberList
FROM rpt_person_phone GROUP BY person_id ORDER BY person_id;
If you're using SQL Server 2016 or prior you can use FOR XML PATH which would go something like this:
SELECT a.person_id
, STUFF((
SELECT ',' + b.phone_number
FROM CTE_rpt_person_phone b
WHERE b.person_id = a.person_id
ORDER BY b.phone_number
FOR XML PATH('')), 1, LEN(','), '') AS phone_numbers
FROM rpt_person_phone a
GROUP BY a.person_id
ORDER BY person_id;
I really haven't done much with STRING_AGG but one thing to look at with the FOR XML PATH method is that it can be slow especially with very large data sets.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply