CODEWITHSUNDEEP
sqlsql-server
drichburg
drichburg

Reputation: 13

Concatenate SQL strings based on

I currently have this data:

appointment_num  order_num modified_proc_desc
"124"             "1"   "DIVISION OF PENILE SKIN BRIDGE"
"125"             "1"   "CIRCUMCISION"
"126"             "1"   "BILATERAL INGUINAL EXPLORATION"
"126"             "2"   "BILATERAL INGUINAL HERNIA/HYDROCELE REPAIR"
"126"             "3"   "POSSIBLE RIGHT ORCHIOPEXY"
"127"             "1"   "EXCISION SCALP CYST"
"128"             "1"   "REPAIR INCOMPLETE CIRCUMCISION"
"129"             "1"   "CIRCUMCISION"

Produced from this table/query:

SELECT [appointment_num]
      ,[order_num]
      ,[modified_proc_desc]
FROM [adv].[dbo].[as_appointment_procs]

I can join modified_proc_desc fields together based on having the same order_num with XML PATH (''). I need to be able to join them sequentially though. For example, for appointment_num "126" above, I want order_num 1, 2, and 3's modified_proc_desc strings concatenated on a new case_procedure field. In other words, I want to only show one appointment with all of the orders on it.

Can anyone point me in the right direction here? Here's an example output:

appointment_num  order_num case_procedure
"124"             "1"   "DIVISION OF PENILE SKIN BRIDGE"
"125"             "1"   "CIRCUMCISION"
"126"             "1"   "BILATERAL INGUINAL EXPLORATION, BILATERAL INGUINAL HERNIA/HYDROCELE REPAIR, POSSIBLE RIGHT ORCHIOPEXY"
"127"             "1"   "EXCISION SCALP CYST"
"128"             "1"   "REPAIR INCOMPLETE CIRCUMCISION"
"129"             "1"   "CIRCUMCISION"

Upvotes: 1

Views: 90

Answers (1)

morgb
morgb

Reputation: 2312

One way is to create an inline sub-query to combine the values into one row:

SELECT DISTINCT 
    appointment_num,
    STUFF(
        (SELECT ', ' + p2.modified_proc_desc
        FROM as_appointment_procs p2
        WHERE p2.appointment_num = p.appointment_num
        ORDER BY p2.order_num
        FOR XML PATH(''), type).value('(./text())[1]','varchar(max)') 
        ,1,2,'') AS case_procedure
FROM as_appointment_procs p

The STUFF command removes the leading comma.

Upvotes: 1

Related Questions