converting multiple rows into a CSV string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

converting multiple rows into a CSV string

Author

Topic

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2001-10-15 : 22:03:16

Hi team,
I got a question about converting multiple rows into a CSV string. I search through your web site and find an article address the similar question and a stored procedure for reference.

However, I am not so skillful to writing array in stored procedure and so hope that team memebers can provide some advice to me.

Basically, I want to condense several rows of a table to a field of antoher table.

More specific, table A contains Col1 (key), Col2, Col3, Col4
and Table B contains Col1 (key), and a Col2

I want to condense the rows from table a to table b. We can do it with sql statement but for performance issue, we want to write it in stored procedure.

Table A may contains data
Col1 Col2 Col3 Col4
123 AAA BBB CCC
124 CCC DDD EEE
123 BBB GGG JJJ
125 EEE KKK EEE

and I want to put the data into table B's Col2 as

Col1 Col2
123 AAA BBB CCC, BBB EEE JJJ
124 CCC DDD EEE
125 EEE KKK EEE
126

The array that i found in your web site that address the question is

Create procedure sp_ParseArray
( @Array varchar(1000),
@separator char(1) )
AS
-- Created by graz@sqlteam.com
set nocount on
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned

-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @array = @array + @separator

-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)

-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
select Array_Value = @array_value

-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
end

set nocount off
go

However, I don't know how to make use of this stored pro. to solve my problem. Hope your expert team can help.

Thx in advance.

Regards,

Yu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources