Mikael Sand - Trying to be different

Today I started reading my new book but I also wanted to find out a bit more about Hyper V, just to do something else. Well it is quite simple: It is the new virtualization technique used in Windows 2008 Server R2. The cool thing (in my view) is that it will use the physical server hardware in a much better and more efficient way. Also, it enables hosting Linux in a Windows Server Centre (Golem?).

Well in the course of looking for information about it I found out that Microsoft has a whole series of webcasts about Windows Server 2008 called “24 hours of Windows Server 2008”. Webcasts are a personal favourite of mine as I was raised by the TV and find that pictures+sound is a great way of learning new things (being spoon-fed). I do not think I need to watch them all but some are mandatory, like the ones about IIS 7.

So, the SQL-thing. As you know I do not talk to SQL server much these days but today I was once again forced to as my colleague wanted to know if there was any good way of concatenating row values, i. e.

CategoryId  ProductName                              
----------- ---------------------------------------- 
1           Chai
1           Chang
2           Coffee
CategoryId  Products                              
----------- ---------------------------------------- 
1           Chai, Chang
2           Coffee

The answer was “No” because there isn’t any function in T-SQL to do this. However I found a really innovative use of FOR XML on this page:

SELECT p1.CategoryId,
          ( SELECT ProductName + ',' 
            FROM Northwind.dbo.Products p2
             WHERE p2.CategoryId = p1.CategoryId
            ORDER BY ProductName
             FOR XML PATH('') 
           )    AS Products
      FROM Northwind.dbo.Products p1
     BY CategoryId ;

The really interesting part is the use of FOR XML PATH. It actually works!!! This has been a problem for a long time. I have never really looked into it until now but it still feels really good to find a nice solution. Maybe me and SQL server might start talking again. Baby steps…