How to output values in a single line 4

[mbusa]

suppose select * from bajwa outputs

os2
ds3
of4
as5
os9


How do I change the query to putput all the values in a single line of type varchar with commas in between the values

os2,ds3,of4,as5,os9




[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[micha123]

How many rows can there be in your table?

 

[mbusa]

no limit. but they will alway sstay less than 100

i guess u are pointing to the total limit of the string that is going to hold these values. right ?

what is the limit anyway. is it 8000 ?

8000 is good enough for me

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[jbenson001]

declare @v varchar(xxx)
set @v= ''
select @v = @v + <col>+ ','
from <table>

select @v

 

[micha123]

Yes... you've got my point...

If performance is not an issue here - you can use a cursor to fetch each row and add it to the string.
Do you need an "un-cursored" solution...?

 

[mbusa]

ok. a little deviation here.

thanx jbenson001 for the solution but heres what the thing is

I have a sproc that is passed a parameter

exec RefSearchHeader '102426,102424,102424'

now I can query a dictionary table to get descriptive values of these parameters and that is actually what i want in the output.

so suppose the sproc is exceuted with '102426,102424,102424'
i want to go grab the text values from the dictionary table and output somethign like table,chairs,seats instead of

table
seats
chairs


and the parameter is dynamic too , so it could be '102426,102424,102424' or '102426,102424,102424,1024426,1343402424,13302424'




[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[Catadmin]

Sounds like you need to design a UDF which counts the number of commas, then runs a While Loop which concatenates your string.

The WHILE Loop is fairly easy. Something like:

Declare @LoopCount int,
        @DescripString Varchar(50)
Set @LoopCount = MyUDF

While @LoopCount > 0
  Begin
   Set @DescripString = @DescripString + ', ' + MyTextValue
   Set @LoopCount = @LoopCount - 1
  END

I've got to go dig up the code on the Delimiter count UDF I have. Once I find it (I keep losing the darned thing), I'll post it. Assuming someone else doesn't post their version first.

I thought about CharIndex and PatIndex, but they don't work for this.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mercwrought]

here is my idea

create table #results ( item varchar(50))

insert into #results
{call RefSearchHeader ('12345,12545')}

declare @v varchar(8000)
	@r varchar(8000)
set @v= ''

DECLARE list CURSOR FAST_FORWARD FOR
SELECT item FROM #results


OPEN list
FETCH NEXT FROM list INTO @r
WHILE @@FETCH_STATUS = 0
BEGIN

select @v = @v + @r+ ','

FETCH NEXT FROM list INTO @r
END

CLOSE list
DEALLOCATE list


select @v

i think that the change needs to be in the RefSearchHeader sp but this should work

 

[Catadmin]

Here's a function a friend gave me that will count the number of times the comma (or whichever character you choose) is in an unlimited size string. Then it will add one so you can get at the last bit of data after the last delimiter.

CREATE FUNCTION ArrayLength (@Array varchar(8000), @Delimeter varchar)  
RETURNS int AS  
BEGIN 
DECLARE @Count int, @Counter int
SET @Count = 0
SET @Counter = 0

IF SUBSTRING(@Array, @Counter, 1) <> @Delimeter
	BEGIN
	  SET @Count = @Count + 1
	END

WHILE @Counter < LEN(@Array)
	BEGIN
	  IF SUBSTRING(@Array, @Counter, 1) = @Delimeter AND LEN(@Array) - @Counter > 0
	     BEGIN
		SET @Count = @Count + 1
	     END
	SET @Counter = @Counter + 1
	END

RETURN @Count
END

Essentially, you set @LoopCounter = Select dbo.ArrayLength(<stringName>, ',')

Hope that helps.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mbusa]

thanx guys.
I tested and tried all of the solutions and they work as described. FYI . I have decideded to use Catadmin's solution
for my final solution.

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[Catadmin]

Glad we could help. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???