Home > ASP.NET, Interview Questions, Programming, SQL, SQL 2005, Tips & Tricks, XML > SQL 2005 – Return query rows as one delimited column

SQL 2005 – Return query rows as one delimited column

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>
VN:F [1.9.22_1171]
Rating: 4.6/5 (8 votes cast)
SQL 2005 - Return query rows as one delimited column, 4.6 out of 5 based on 8 ratings
  1. June 30th, 2009 at 23:36 | #1

    Cool post, just subscribed.

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
  2. July 7th, 2009 at 08:36 | #2

    I have already seen it somethere…
    AnnaHopn

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    • July 8th, 2009 at 22:23 | #3

      Well, everything is out there somewhere.
      The question is how easy to find it.

      VN:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
  3. July 9th, 2009 at 16:11 | #4

    Not sure that this is true:), but thanks for a post.

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
  1. July 14th, 2009 at 19:13 | #1
  2. July 20th, 2009 at 03:52 | #2

Subscribe without commenting

SEO Powered by Platinum SEO from Techblissonline
watch free movies