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 PeopleSELECT 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 551 661 772 882 99
Now, imagine you want a result like this:
OrderID ProductID-------------- -----------------1 55,66,772 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]
Remember Me
a@href@title, strike
Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.