Solved

SELECT Query returns values seperated by comma

Posted on 2009-03-30
7
193 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:JackW9653
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24021280
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
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24021319
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
 

Author Comment

by:JackW9653
ID: 24021472
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 24021740
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24021753
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
 

Author Closing Comment

by:JackW9653
ID: 31564444
Thanks CG, it worked great!! I added a space after the comma for cosmetics and plugged in the Update statement.
0
 

Author Comment

by:JackW9653
ID: 24022049
Thanks 8080 Driver for the nice bit of code, but CG's XML was much simpler to implement within the SSIS package.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
A short film showing how OnPage and Connectwise integration works.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now