SELECT Query returns values seperated by comma

Hello Experts,
I have a very simple SELECT statement that returns multiple rows. What i want is those rows returned as 1 row with the various values seperated by a comma.

97010 - Hot/Cold Pack
97014 - Electrical Stimulation
97035 - Ultrasound/Phonophoresis
97110 - Other Exercise
97110 - Pend - CCW

Should read: 97010 - Hot/Cold Pack, 97014 - Electrical Stimulation, 97035 - Ultrasound/Phonophoresis, 97110 - Other Exercise, 97110 - Pend - CCW



SELECT Treatment
FROM tblPlanTreatments
WHERE Encounter_code = 29122
ORDER BY Treatment

Open in new window

JackW9653Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
Jack,
Ah yes, embeded spaces.  Then add this extra trick so you do not replace those with a comma.  If the '~' does not work because it may appear in your data, change it to any other character or combo.
SELECT DISTINCT  Treatments = REPLACE(REPLACE((SELECT Treatment+'~' AS [data()]
                   FROM tblPlanTreatments
                   WHERE Encounter_code = 29122
                   ORDER BY Treatment
                   FOR XML PATH ('')), '~ ', ','),'~', '')

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
2 questions:
? why does it have to be that way? could this not be solved in the client application code?
? you will need a function/procedure for this, would that be a no-go?
0
 
Chris LuttrellSenior Database ArchitectCommented:
Try this xml trick:
SELECT DISTINCT  Treatments = REPLACE((SELECT Treatment AS [data()]
                   FROM tblPlanTreatments
                   WHERE Encounter_code = 29122
                   ORDER BY Treatment
                   FOR XML PATH ('')), ' ', ',')

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
JackW9653Author Commented:
angelIII: this select is part of an update rouutine that will run against another SQL table that will be sent to an Informix database for reporting. So no it can't be in the app code. As for the function/procedure i don't see why that would be a problem as this is only 1 part of a large SSIS package.

CG: Cool idea, but here is the output:
97010,-,Hot/Cold,Pack,97014,-,Electrical,Stimulation,97035,-,Ultrasound/Phonophoresis,97110,-,Other,Exercise,97110,-,Pend,-,CCW
0
 
8080_DiverCommented:
Jack,
this select is part of an update rouutine that will run against another SQL table that will be sent to an Informix database for reporting
So, why not provide either the dataset or run the routine to update the other SQL table and then let Informix access the updated data for reporting?
If you absolutely have to do it this way, then I would recommend creating a stored procedure that returns the results you want.  Now, the caveat there is that you are going to run into built in limitations on how much stuff you can concatenate and return as you result . . . 8000 characters if it is not Unicode and 4000 if it is.

See if the following works for you.

CREATE PROCEDURE ReturnConcatenatedData
         @Encounter_Code   INT,
	@TheResult	VarChar(8000)
AS
BEGIN
 
  DECLARE @TempStr AS VarChar(4000);
	
  DECLARE cTheCursor CURSOR FAST_FORWARD
	                   FOR SELECT Treatment
                                FROM tblPlanTreatments
                                WHERE Encounter_code = @Encounter_Code
                                ORDER BY Treatment
 
	                       
  OPEN cTheCursor;
	
  FETCH cTheCursor INTO @TempStr;	
	
  WHILE @@Fetch_Status = 0
  BEGIN
    IF @TheResult = ''
    BEGIN
      SET	@TheResult = TRIM(@TempStr);
    END
    ELSE 
    BEGIN
      SET @TheResult = @TheResult + ',' + TRIM(@TempStr);
    END
  END
	                      
  RETURN
	
END	

Open in new window

0
 
JackW9653Author Commented:
Thanks CG, it worked great!! I added a space after the comma for cosmetics and plugged in the Update statement.
0
 
JackW9653Author Commented:
Thanks 8080 Driver for the nice bit of code, but CG's XML was much simpler to implement within the SSIS package.
0
All Courses

From novice to tech pro — start learning today.