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

Transact-SQL (2000)

Converting Multiple Rows into a CSV String

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-05-19 : 07:57:43

Well, I'm pretty sure I've done this before, but the way I'm doing it doesn't work.

I've looked at the article Converting Multiple Rows into a CSV String which is slightly different in that the update happens on the a table which contains PK plus DATA, whereas I'm trying to update a table that just contains the DISTINCT PK by joining it to the table containing PK + DATA.

Is there a way to do it without creating a temporary PK + DATA table and UPDATEing that in-situ?

In the following example #TEMP_02 is what I want to achieve, but that doesn't work. #TEMP_03 works fine (which is in the style of the article)

Perhaps tellingly the #TEMP_02 UPDATE shows "(2 row(s) affected)", whereas the #TEMP_03 UPDATE shows "(7 row(s) affected)"


CREATE TABLE #TEMP_01
(
	T1_Ref		varchar(10),
	T1_Client	varchar(10)
)

CREATE TABLE #TEMP_02
(
	T2_Ref		varchar(10),
	T2_ClientList	varchar(8000)
)

INSERT INTO #TEMP_01
          SELECT 'XXX001', 'CLIENT1' 
UNION ALL SELECT 'XXX001', 'CLIENT2'
UNION ALL SELECT 'XXX001', 'CLIENT3'
UNION ALL SELECT 'XXX002', 'CLIENT2'
UNION ALL SELECT 'XXX002', 'CLIENT3'
UNION ALL SELECT 'XXX002', 'CLIENT4'
UNION ALL SELECT 'XXX002', 'CLIENT5'

INSERT INTO #TEMP_02
(
	T2_Ref
)
SELECT DISTINCT T1_Ref
FROM	#TEMP_01

DECLARE	@strLastRef	varchar(10),
	@strClientList	varchar(8000)
SELECT @strLastRef = '', @strClientList = ''
UPDATE U SET
	@strClientList = T2_ClientList = 
		CASE WHEN @strLastRef = T1_Ref 
			THEN COALESCE(@strClientList+' ', '') + T1_Client 
			ELSE T1_Client 
			END,
	@strLastRef = T1_Ref
FROM	(
		SELECT TOP 100 PERCENT
			T1_Ref, T1_Client
		FROM	#TEMP_01
		ORDER BY T1_Ref
	) X
	JOIN #TEMP_02 U
		 ON T2_Ref = T1_Ref

PRINT 'Original data #TEMP_01'
SELECT	*
FROM	#TEMP_01
ORDER BY T1_Ref, T1_Client

PRINT 'CSV Results #TEMP_02'
SELECT	*
FROM	#TEMP_02
ORDER BY T2_Ref

CREATE TABLE #TEMP_03
(
	T3_Ref		varchar(10),
	T3_Client	varchar(10),
	T3_ClientList	varchar(8000)
)

INSERT INTO #TEMP_03
(
	T3_Ref, T3_Client
)
SELECT	T1_Ref, T1_Client
FROM	#TEMP_01

SELECT @strLastRef = '', @strClientList = ''

UPDATE	U
SET
	@strClientList = T3_ClientList = 
		CASE WHEN @strLastRef = T3_Ref 
			THEN COALESCE(@strClientList+' ', '') + T3_Client 
			ELSE T3_Client 
			END,
	@strLastRef = T3_Ref
FROM	#TEMP_03 U

PRINT 'CSV Results #TEMP_03'
SELECT	T3_Ref, MAX(T3_ClientList)
FROM	#TEMP_03 U
GROUP BY T3_Ref

DROP TABLE #TEMP_01

DROP TABLE #TEMP_02

DROP TABLE #TEMP_03

Thanks

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-19 : 09:38:01

Read the comments, see the UDF solution. It is by far the most efficient, shortest, clearest and most accurate way to do it.

You just simply define the situations in which you need to generate a CSV list; for each one, think about what the input is (i.e., Country) and what the output is (i.e., a list of Customers that are located in that country.)

Then write a UDF:


create function CustList(@Country as varchar(100))
returns varchar(8000) as
as
begin
  declare @ret varchar(8000);
  set @ret = ''

  select @ret = @ret + ', ' + CustomerName
  from Customers
  where Country = @Country
  order by CustomerName

  return substring(@ret,3,8000)
end

then you simply select from your input table (Countries, in this example) and call the function once for each input:


select Country, dbo.CustList(@Country) as Customers
from Countries

easy as that ! Write as many UDF's as you need to handle as many lists as you need. You can easily have multiple CSV lists in 1 SELECT or whever you need them.

- Jeff

Kristen
Test

22859 Posts

Posted - 2005-05-19 : 10:47:46

Didn't read the comments, sorry. Thanks for that Jeff ... off to fully investigate.

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-19 : 10:54:50

Hi Kristen,
We had a similar problem inhouse, but the reverse, we had to parse a CSV string into a table, we initially used a temp table, but later got it to work without the temp table

the script below is our working solution. A bit modified though
Afrika

create procedure InsertNames (@InArray as Varchar(8000))
AS
 
 --declarations
 declare @Delimter Char(1)
 declare @Element Varchar(8000)
 declare @continue int
 declare @cPos int
 
 --
 set @continue = 1
 set @delimter = ','

 --add a trailing delimiter to the array
 select @InArray = @InArray + @Delimter
 
 --create a temporary table to hold each element
 WHILE (@continue >= 1)
 BEGIN
 select @cPos = CHARINDEX(@Delimter, @InArray)
 select @Element = Rtrim(Ltrim(SUBSTRING(@InArray,1, @cPos -1)))
 
 BEGIN
 ********* INSERT Names (n) VALUES (@Element)
 END
 
 select @InArray = SUBSTRING(@InArray, @cPos + 1, DATALENGTH(@InArray))
 select @continue = CHARINDEX(@Delimter, @InArray)
 END
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-19 : 11:29:43

You shouldn't have to research too much more, I hope I gave a pretty clear answer on a good way to get it done !

Let me know if you have any questions. The old UPDATE trick was very useful before SQL 2000, when you didn't have UDF's of course.

- Jeff

Kristen
Test

22859 Posts

Posted - 2005-05-26 : 01:48:58

Worked a treat thanks. Just took a little while to write sufficient FN's, but I feel much more comfortable with the solution now its done.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources