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]