JackW9653
asked on
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
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
Try this xml trick:
SELECT DISTINCT Treatments = REPLACE((SELECT Treatment AS [data()]
FROM tblPlanTreatments
WHERE Encounter_code = 29122
ORDER BY Treatment
FOR XML PATH ('')), ' ', ',')
ASKER
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,9701 4,-,Electr ical,Stimu lation,970 35,-,Ultra sound/Phon ophoresis, 97110,-,Ot her,Exerci se,97110,- ,Pend,-,CC W
CG: Cool idea, but here is the output:
97010,-,Hot/Cold,Pack,9701
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
ASKER
Thanks CG, it worked great!! I added a space after the comma for cosmetics and plugged in the Update statement.
ASKER
Thanks 8080 Driver for the nice bit of code, but CG's XML was much simpler to implement within the SSIS package.
? 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?