clean up on result set?!?!?!

Does anyone know how to get rid of commas and spaces in the result set from my query??

Here is my SQL query:

SELECT records.result, company.company,
records.firstn + ' ' + records.lastn AS DONOR, records.donorid,
records.collecteddate, records.reason, records.lab,
records.speciminid,
records.amphetamine + ' ' + records.butalbital + ' ' + records.cocaine
+ ' ' + records.codeine + ' ' + records.heroin + ' ' + records.marijuana
+ ' ' + records.methadone + ' ' + records.methamphetamine + ' ' +
records.morphine + ' ' + records.phencyclidine + ' ' + records.propoxyphene
+ ' ' + records.otherdrug AS DRUGS, records.status,
records.reportcontact, records.reportdate, records.verifieddate,
records.signature, records.dot, records.nonregulated,
records.reportsentdate, records.form, records.recondate,
records.reconlab
FROM records INNER JOIN
company ON records.mrocomid = company.mrocomid
WHERE (records.recordnum = ?)

Obviously, "DRUGS" is the result set I am talking about....

But I need it to LOOK nice, and right now it comes out something like this....

" Methamphetamine Phencyclidine"

Need it to look like this:

Methamphetamine, Phencyclidine

or

Methamphetamine
Phencyclidine


Any ideas anyone???

Kristin

why u use those quotes in your querry ? what type of database is this ? why it wont work without those qoutes ?

shinu

It is an SQL Server 2000 database. It is the rule for concantenating....the ' ' puts a space in between each of the results.....

otherwise it looks like this:

MethadoneMethamphetamineOpiates

see what I mean? How can I work around this, if I put ',' then my results look like this...

,,,,,Methadone,,Methamphetamine,,,,Opiates

ugh.

Hi again,

To solve your problem, use this query:

SELECT records.result, company.company,
records.firstn + ' ' + records.lastn AS DONOR, records.donorid,
records.collecteddate, records.reason, records.lab,
records.speciminid,
CASE WHEN records.amphetamine IS NULL THEN '' ELSE records.amphetamine + ', ' END +
CASE WHEN records.butalbital IS NULL THEN '' ELSE records.butalbital + ', ' END +
CASE WHEN records.cocaine IS NULL THEN '' ELSE Records.cacaine + ', ' END +
CASE WHEN records.codeine IS NULL THEN '' ELSE records.codeine + ', ' END +
CASE WHEN records.heroin IS NULL THEN '' ELSE records.heroin + ', ' END +
CASE WHEN records.marijuana IS NULL THEN '' ELSE records.marijuana + ', ' END +
CASE WHEN records.methadone IS NULL THEN '' ELSE records.methadone + ', ' END + CASE WHEN records.methamphetamine IS NULL THEN '' ELSE records.methamphetamine + ', ' END +
CASE WHEN records.morphine IS NULL THEN '' ELSE records.morphine + ', ' END +
CASE WHEN records.phencyclidine IS NULL THEN '' ELSE records.phencyclidine + ', ' END +
CASE WHEN records.propoxyphene IS NULL THEN '' ELSE records.propoxyphene + ', ' END +
CASE WHEN records.otherdrug IS NULL THEN '' ELSE records.otherdrug END AS DRUGS, records.status,
records.reportcontact, records.reportdate, records.verifieddate,
records.signature, records.dot, records.nonregulated,
records.reportsentdate, records.form, records.recondate,
records.reconlab
FROM records INNER JOIN
company ON records.mrocomid = company.mrocomid
WHERE (records.recordnum = ?)

This will give the output you want, if there are null values in the drug fields. If there are values there (spaces) then use another expression to test (instead of null values). Easy as pie. Any questions, feel free to ask.

Grtz.©

M.