GeekzRule.com - SQL Server - Concatenating Many Rows Into A Single Text String (also known as de-normalization)

Tuesday, June 09, 2009

SQL Server - Concatenating Many Rows Into A Single Text String (also known as de-normalization)

I came across a wierd thought about how to de-normalize data, or how to quickly group rows of data into a single delimited string. I have come up with two scenarios:

First, if you just want to quickly concatenate everything, you can do the following:

DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)

Above, you end up with a variable with everything in it.

In the real world, the above example doesn't help most of us. Using SQL Server 2005, you can use XML PATH to get some cool results. Imagine you had a table called ORDERS that looks like the following:

OrderID                        ProductID
--------------              -----------------
1                                55
1                                66
1                                77
2                                88
2                                99

Now, imagine you want a result like this:

OrderID                        ProductID
--------------              -----------------
1                                55,66,77
2                                88,99

There is no quick and easy way to do this in SQL Server, until now:

Select
   Main.OrderID
   , Left(Main.ProductID,Len(Main.ProductID)-1) As 'ProductID'
From
   (
      Select distinct
         order2.OrderID
         , (
               Select
                  order1.ProductID + ',' AS [text()]
               From
                  dbo.Orders order1
               Where
                  order1.OrderID = order2.OrderID
               ORDER BY
                  order1.OrderID
               For XML PATH ('')
            ) [ProductID]
From dbo.Orders order2
   ) [Main]

Tuesday, June 09, 2009 8:06:30 PM (Mountain Standard Time, UTC-07:00)

Comments [0] -

Name
E-mail
Home page

	Remember Me
Comment (Some html is allowed: `a@href@title, strike`) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.
Enter the code shown (prevents robots):
Live Comment Preview