Concatenate SQL strings based on

Question

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"

does this help? http://stackoverflow.com/questions/8631210/group-concat-order-by — Marshall Tigerus, Jul 19 '16 at 15:05
http://stackoverflow.com/questions/38285250/concatenate-fields-in-order-sql-server/38285399#38285399 — TheGameiswar, Jul 19 '16 at 15:13

score 1 · Accepted Answer · answered Jul 19 '16 at 15:17

1

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.

answered Jul 19 '16 at 15:17

morgb

2,252
2
14
14

I would suggest `SELECT appointment_num .... GROUP BY appointment_num` here, rather than `SELECT DISTINCT appointment_num...` because of the processing order. If you use `GROUP BY` it will first remove the duplicates, then execute the subquery for each unique value of `appointment_num`, whereas `DISTINCT` will evaluate the subquery once for every row, then after this it will remove the duplicates. It may not make a significant difference, but then again it might, and every little helps. – GarethD Jul 19 '16 at 15:27
Thank you, this is exactly what I was looking for. Thanks for the education. I'll also look at GarethD's response here as well. – drichburg Jul 19 '16 at 16:32

Concatenate SQL strings based on

1 Answers1