output sql table as comma delimited string 3

[pandapark]

I've got a SQL table with column ID of type int.
it will have anything from 1 - 10 rows with values such as
ID
--
10
12
20

I'd like to return the values as a comma delimited string i.e. 10, 12, 20

I've created the UDF below

CREATE Function dbo.ConcatDesc (@ID int) 
   returns varchar(1000)
AS
BEGIN 

Declare @Str as varchar(1000)

SELECT @Str = COALESCE(@Str + ', ', '')
FROM tblTempID
WHERE ID= @ID

RETURN @Str

END

and called it in my asp as

strTempID = "Select BusinessDir.dbo.ConcatDesc(ID) as MyID from tblTempID"
set objRS8 = objCon.execute(strTempID)

then response.write " & objRS8("MyID") & "

but nothing comes back, nor in QA

any ideas what I'm doing wrong

thanks
kim

 

[gmmastros]

Coalesce does not work the way you think it does. Coalesce will return the first Non-Null value.

Ex:

Declare @A Integer
Declare @B Integer
Declare @C Integer

Set @A = NULL
Set @B = 7
Set @C = 12

Select Coalesce(@A, @B, @C) 

Set @B = NULL

Select Coalesce(@A, @B, @C)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[pandapark]

oh ok

is there some way i could do what i want to do ?

thanks

 

[gmmastros]

Unfortunately, the only way I know to do this would be to loop through the records and build the string.

Declare @Temp Table(RowId Integer Identity(1,1), Id Integer)

insert Into @Temp(Id) Select ID From tblTempId

Declare @i Integer
Declare @Max Integer

Select @Max = Max(RowId) From @Temp

Declare @Result VarChar(8000)
Set @Result = ''

Set @i = 1
While @i <= @Max
	Begin
		If @Result = ''
			Set @Result = Convert(VarChar(10), (Select Id From @Temp Where RowId = @i))
		Else
			Set @Result = @Result + ',' + Convert(VarChar(10), (Select Id From @Temp Where RowId = @i))
		Set @i = @i + 1
	End



Select @Result

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

If you only want the results to show in Query Analyzer, go to Tools > Options > Results tab. Select Output to Text and then choose Comma Delimited Values.

-SQLBill

Posting advice: FAQ481-4875

 

[pandapark]

hi

i do want to use them in a recordset within my asp.
i've tried implementing george's code but get an error ?

 

[gmmastros]

What error are you getting?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

Bill excellent tip.. didn't know you could do that, saves lots of coding time.

 

[pandapark]

sorry george, bit of a novice at this......

it is saying UDF must include name and text

 

[vongrunt]

Let's get back to original UDF. Check this line:

SELECT @Str = COALESCE(@Str + ', ', '')

I don't see any value from tblTempID here... that's why UDF always returns ''.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

I did not make my code in to a function. It was intended to run in QA for testing purposes. Try this.

ALTER Function dbo.ConcatDesc (@ID int) 
   returns varchar(1000)
AS
BEGIN 

	Declare @Temp Table(RowId Integer Identity(1,1), Id Integer)
	
	Insert Into @Temp(Id) Select ID From tblTempId
	
	Declare @i Integer
	Declare @Max Integer
	
	Select @Max = Max(RowId) From @Temp
	
	Declare @Result VarChar(8000)
	Set @Result = ''
	
	Set @i = 1
	While @i <= @Max
		Begin
			If @Result = ''
				Set @Result = Convert(VarChar(10), (Select Id From @Temp Where RowId = @i))
			Else
				Set @Result = @Result + ',' + Convert(VarChar(10), (Select Id From @Temp Where RowId = @i))
			Set @i = @i + 1
		End
	
	
	
	Return @Result
End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[pandapark]

brilliant !

thank you

 

[gmmastros]

You're welcome.

Please, do me a favor and make sure that the data returned is EXACTLY what you expect it to be. I noticed that the function has @ID as an input, but you'll notice that I'm not using it anywhere. I don't understand your data (and I shouldn't have to). If the data is correct, you can safely remove the parameter and then change your front end code to NOT pass the parameter.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[pandapark]

will do...........thanks again

 

[vongrunt]

Um... maybe I missed something...

alter function dbo.ConcatDesc()
	returns varchar(1000)
as
begin
	declare @Str varchar(1000)
	select @Str = coalesce(@str + ',', '') + convert(varchar(10), ID)
	from tblTempId
	where id is not null
	order by ID asc

	return @Str
end
go

select dbo.ConcatDesc()

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

Oh man. I'm such an idiot. I suppose that's why I keep hanging around these forums. I keep learning things I never knew before.

I was confused by the Coalesce function, which I don't use very often. I now realize that the Coalesce function only exists (in this UDF) to prevent the first comma from appearing in the output.

Without the coalesce function, the output would be something like: ",1,2,3" With it, the output is: "1,2,3"

I also didn't realize you could add/combine things in a set based way.

So, vongrunt, thank you, thank you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[pandapark]

oh ok...........two for the price of one !

one quick question re. george's function

i call it like such in my asp (works great)

strTempID = "Select BusinessDir.dbo.ConcatDesc1(ID) as MyID from tblTempID"
set objRS8 = objCon.execute(strTempID)

my table tblTempID has lots of records, I'd like the function to only select those where the column (uniqueID in tblTempID) is equal to request.form("uniqueID") in my asp page

so the table may look like this

Abbatoir, Business, C5
another, Business, C5
again, Business, 47
and again, Business, 47

i only want the function to retrieve records = C5 (for example), kind of like (but this doesn't work it just appends all the records from tbltempID)

strTempID = "Select BusinessDir.dbo.ConcatDesc1(ID) as MyID from tblTempID where uniqueid = '" & request.form("uniqueid") & "'"
set objRS8 = objCon.execute(strTempID)

thanks again

here is the function, modified to insert text not int

CREATE Function dbo.ConcatDesc1 (@ID varchar(150)) 
   returns varchar(1000)
AS
BEGIN 

    Declare @Temp Table(RowId Integer Identity(1,1) , Id varchar(150))
    
    Insert Into @Temp(Id) Select ltrim(ID) From tblTempId
    
    Declare @i Integer
    Declare @Max Integer
    
    Select @Max = Max(RowId) From @Temp
    
    Declare @Result VarChar(8000)
    Set @Result = ''
    
    Set @i = 1
    While @i <= @Max
        Begin
            If @Result = ''
                Set @Result = Convert(VarChar(1000), (Select Id From @Temp Where RowId = @i))
            Else
                Set @Result = @Result + ',' + Convert(VarChar(1000), (Select Id From @Temp Where RowId = @i))
            Set @i = @i + 1
        End 
        
    Return @Result
End

 

[gmmastros]

I recommend you change the UDF to be like vongrunt suggested. Performance will be better. You could pass the UniqueId in to the function and then use it there to filter the data.

alter function dbo.ConcatDesc[red](@UniqueId VarChar(100))[/red]
    returns varchar(1000)
as
begin
    declare @Str varchar(1000)
    select @Str = coalesce(@str + ',', '') + convert(varchar(10), ID)
    from tblTempId
    where id is not null
          [red]And UniqueId = @UniqueId[/red]
    order by ID asc

    return @Str
end

I'm no ASP expert, but something like this should do the trick.

strTempID = "Select BusinessDir.dbo.ConcatDesc1('" & request.Form("UniqueID") & "') as MyID from tblTempID"
set objRS8 = objCon.execute(strTempID)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom