Monday, October 03, 2005

SQLServer 2000: Concatenate fields in a table

Here's a handy way to create a comma-separated list from a small lookup table. I put the most interesting statement in bold.

-- BEGIN CODE

-- silence the "rows affected" message
SET NOCOUNT ON ;

-- create a temp table
CREATE TABLE #temp(id int identity(1,1) not null, state varchar(50) not null);
-- insert a few states for our example
INSERT INTO #temp(state) values('AK');
INSERT INTO #temp(state) values('CA');
INSERT INTO #temp(state) values('HI');
INSERT INTO #temp(state) values('OK');
INSERT INTO #temp(state) values('TX');

-- declare a variable large enough to hold the needed results
DECLARE @strg varchar(1000) ;

-- collect fields (ordered any way you want)
-- you can also do "top 10"
SELECT @strg = COALESCE(@strg + ',', '') + State
FROM #temp t
ORDER BY State DESC

PRINT 'My States: ' + @strg ;

DROP TABLE #temp ;
-- END CODE

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home