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? stackoverflow.com/questions/8631210/group-concat-order-by — Marshall Tigerus
– Marshall Tigerus, Commented Jul 19, 2016 at 15:05

morgb · Accepted Answer · 2016-07-19 15:17:41Z

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, 2016 at 15:17

morgb

2,3322 gold badges14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GarethD Over a year ago

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.

drichburg Over a year ago

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.

Collectives™ on Stack Overflow

Concatenate SQL strings based on

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related