Here's an example of how to create a delimited list, using a field of rows of data and COALESCE.

CREATE TABLE #tmp (ID INT IDENTITY, Value VARCHAR(10))

INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)

DECLARE @vcList VARCHAR(100)
SELECT @vcList = COALESCE(@vcList + ';', '')
+ ISNULL(Value, '0')
FROM #tmp
-- ORDER BY might not work
PRINT @vcList
Resulting in:
10;1;2;3;4;5;6;7;8;9

NOTE:
Don't be surprised when ORDER BY doesn't work.  

'SQL Server query processor builds an different execution plan when expressions are applied to columns in the query's ORDER BY clause, than when those same expressions are applied to columns in the query's SELECT list. The decision made by the query processor is based on the cost of possible execution plans.'


Posted in: SQL  Tags:

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

You bet!

  1. On your SQL 2005/8 instance create a linked SQL 2000 server
  2. Run your SQL 2005 query using something like PIVOT or UNPIVOT against the SQL 2000 linked server table.
SELECT  [ND],
        [SD],
        [TN],
        [OR],
        [VA]
FROM    (
         SELECT State,
                SalesAmt
         FROM   sqltest.northwind.dbo.Sales
        ) p PIVOT ( SUM(SalesAmt) FOR State IN ([ND], [SD], [TN], [OR], [VA]) ) AS pvt
 
Here's a link to an article example about PIVOT, UNPIVOT.
 

Posted in: SQL  Tags:

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2010 The SQL DBA