.NET Tips and Tricks

Once in a while we need to return data from SQL as delimited string of values and not as rows. There are several ways to do that, but personally i like the next one.

SELECT CustomerID AS 'data()'
FROM Customer
FOR XML PATH('')
-- This will result one column with a value like this: 1 2 3 4 5 6 7 8

-- And to get the column as part of some other query:
SELECT CustomerID,
       (SELECT OrderID AS 'data()'
       FROM Order O WHERE O.CustomerID = C.Customer ID
       FOR XML PATH('')) AS OrderList
FROM Customer C

-- The result of the above query will be something like this :
-- CustomerID	| OrderList
-- --------------------------------
-- 1		    | 1 2 5 9 90 200
-- 2		    | 2 7 10 22
-- 3		    | 12 17 246
-- 4		    | 83 92 1002

-- And of course we could return it as XML
SELECT CustomerID AS '@id',
       (SELECT OrderID AS 'data()'
       FROM Order O WHERE O.CustomerID = C.Customer ID
       FOR XML PATH('')) AS '@ordelist'
FROM Customer C
FOR XML PATH('customer'), ROOT('root')

-- The result of the above query will be something like this :
<root>
  <customer id="1" orderlist="1 2 5 9 90 200"/>
  <customer id="2" orderlist="2 7 10 22"/>
  <customer id="3" orderlist="12 17 246"/>
  <customer id="4" orderlist="83 92 1002"/>
</root>

A few posts you might find interesting:

1. C# Set method timeout using Generics
2. C# Inheritance Question
3. C# LINQ Teaser
4. SpeedTrace – .NET Profiler and Tracer
5. C# Protected Internal