Use COALESCE to put a column of data into a delimited list variable

by John 31. December 2009 01:41

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.'

Tags:
Categories: SQL
E-mail | Kick it! | DZone it! | del.icio.us
Permalink | Comments (0) | Post RSSRSS comment feed

Can we use the SQL 2005 engine for queries against a SQL 2000 linked server?

by John 28. December 2009 04:40

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.
 
Tags:
Categories: SQL
E-mail | Kick it! | DZone it! | del.icio.us
Permalink | Comments (0) | Post RSSRSS comment feed