if output > 1?

Question

Post reply

if output > 1?

QPR

SSC Eights!

Points: 814
More actions
September 20, 2005 at 10:59 pm

#95249

Must be easy for the gurus out there!
I have an SP that is a simple select with a couple of inner joins.
If there is more than 1 text value returned I would like to comma delimit them.
Therefore the output would appear as either.....
text1 or text1,text2 or text1,text2,text3 etc
Ordinarily I'd simply select SomeField & ',' but obviously we don't want the comma if only 1 is returned.
Cheers

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

Try

text1 + ISNULL(',' + text2, '') + ISNULL(',' + text3, '') etc...

Probably need to do some casting for non-char fields.

--------------------
Colt 45 - the original point and click interface

QPR SSC Eights! Points: 814 More actions · Answer 2

Hi Phil,

Sorry I didn't explain as well as I could have.

There is only 1 text field in the select statement. The commas are needed if the field has more than 1 result. Example "select friends from mylist where age > 25"

1 result would appear as..... John

> 1 result should return John, Jane, Peter, Sam

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

QPR - if you're talking # of rows, why don't you check @@ROWCOUNT - if > 1 then delimit, else return asis ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

QPR SSC Eights! Points: 814 More actions · Answer 4

sounds perfect!

Are you able to share the it of code you are referring to please as I've never used/tested for @@rowcount....

select foo from bar

if @@rowcount > 1 then foo = foo & "," ????

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

This could be one of the ways to do it...

declare @result varchar(255)
set @result = ''
select txtField from myTable where condition....
if @@rowcount > 1
   begin
      select @result = COALESCE(@result + txtField + ',', '') 
      from myTable where condition...
      PRINT @result
   end

..or you could check the count(*) of your select...

declare @result varchar(255)
set @result = ''

if (select count(txtField) from myTable where condition....) > 1
   begin
      select @result = COALESCE(@result + txtField + ',', '') 
      from myTable where condition...
   end
PRINT @result

It'd be better to go with @@rowcount since I assume there's a possiblity that you could get 0 rows back as well ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

QPR SSC Eights! Points: 814 More actions · Answer 6

Excellent!

One last question....

The results (Query Analyzer.... text results) return...

synopsis

------

Residential

Rural

(2 row(s) affected)

Residential,Rural,

I only want to return Residential, Rural to the calling function.

How can I supress the earlier result section? I want to return just what the print statement returns to QA. Also I'd like to lose the trailing comma where applicable.

Continued thanks

sushila SSC-Dedicated Points: 35293 More actions · Answer 7

sorry - am in the middle of something - promise to get back in an hour or so!!!

**ASCII stupid question, get a stupid ANSI !!!**

QPR SSC Eights! Points: 814 More actions · Answer 8

Panic over, I solved it!

I put the initial select 9test) in a cursor and then looped through adding a comma if necessary.

Thanks for all the help

sushila SSC-Dedicated Points: 35293 More actions · Answer 9

QPR - NO NO NO...Don't let the "CursorsAreEVIL" police catch you saying this (& this site is crawling with them...)

Try & use a stored procedure or function instead....let me give you the outline and you can "fill it in"...

CREATE PROCEDURE procResult 
@Result VarChar(255) OUTPUT
AS

SELECT @Result = COALESCE(@Result + txtField + ',', '') FROM myTable
IF ASCII(RIGHT(@Result, 1)) = 44
   BEGIN
    SET @Result = SUBSTRING(@Result, 1, len(@result) - 1)
   END
RETURN
GO

DECLARE @ResultfromProc VarChar(255)
EXEC procResult @Result = @ResultfromProc OUTPUT
SELECT @ResultfromProc

OR

CREATE FUNCTION dbo.DelimitedString()
RETURNS VarChar(4000)
AS 


BEGIN
DECLARE @Result VarChar(4000)
SELECT @Result = COALESCE(@Result + txtField + ',', '') FROM myTable
 IF ASCII(RIGHT(@Result, 1)) = 44
    BEGIN
    SET @Result = SUBSTRING(@Result, 1, len(@result) - 1)
    END
RETURN @Result
END
GO

SELECT dbo.DelimitedString()

**ASCII stupid question, get a stupid ANSI !!!**

philcart SSC-Forever Points: 47796 More actions · Answer 10

Yes do use a function, wouldn't do to give the "CursorsAreEVIL" police more ammunition

Also, correct me if I'm wrong, but if you use the ISNULL() function as below, do you need the IF statement to trim off the trailing comma?

CREATE FUNCTION dbo.DelimitedString()
RETURNS VarChar(4000)
AS 

BEGIN
DECLARE @Result VarChar(4000)
SELECT @Result = ISNULL(',' + txtField, '') FROM myTable
 IF ASCII(RIGHT(@Result, 1)) = 44
    BEGIN
    SET @Result = SUBSTRING(@Result, 1, len(@result) - 1)
    END
RETURN @Result
END
GO

SELECT dbo.DelimitedString()

--------------------
Colt 45 - the original point and click interface

sushila SSC-Dedicated Points: 35293 More actions · Answer 11

Phill - using ISNULL the way you have it only adds a leading comma instead of a trailing one...also need to include the column...

SELECT @Result = txtField + ISNULL(',' + txtField, '') FROM myTable

as for "...correct me if I'm wrong"...I wouldn't dare..

**ASCII stupid question, get a stupid ANSI !!!**

philcart SSC-Forever Points: 47796 More actions · Answer 12

"...only adds a leading comma instead of a trailing one..."

Oh well, trim off the trailing or trim off the leading ... which way is better ??

Maybe you could throw a case statement in there ... eg:

 SELECT @Result = @Result + 
   CASE
    WHEN LEN(@Result) < 1 THEN ISNULL([txtField], '')
    ELSE ISNULL(',' + [txtField], '')
   END
 FROM myTable

"...also need to include the column..."

SELECT @Result = ISNULL(',' + txtField, '') FROM myTable

This will return ',<field value>' if the value is not null, if it is null then it just returns ''

"...I wouldn't dare..."

I'm not that bad am I ??

--------------------
Colt 45 - the original point and click interface

sushila SSC-Dedicated Points: 35293 More actions · Answer 13

Applause for the CASE statement...

"...also need to include the column..."...My bad..what I meant to say was to concat the variable @Result which you'd left out....

SELECT @Result = @Result + ISNULL(',' + txtField, '') FROM myTable

"I'm not that bad am I ??"...No, no - I meant I wouldn't dare 'cos I'm not in the habit of questioning the wisdom of savants...

**ASCII stupid question, get a stupid ANSI !!!**

philcart SSC-Forever Points: 47796 More actions · Answer 14

"...I'm not in the habit of questioning the wisdom of savants..."

Question, question it's highly unlikely that one person knows everything.

http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=634f7f84-6c10-4163-9890-faf1ab55f66a

--------------------
Colt 45 - the original point and click interface