TSQL Beginners Challenge 4 - Concatenating values from multiple rows

http://beyondrelational.com/blogs/tcb/archive/2010/03/28/TSQL-Beginners-Challenge-4-Concatenating-values-from-multiple-rows.aspx

 Solution :

 

SELECT id, Stuff(name, ( Len(name) - 3 ), Len(name), '') FROM (SELECT DISTINCT id,
(SELECT a.name + ' ' + b.chk + ' ' FROM tc4 a WHERE a.id = b.id FOR XML PATH('')) AS name FROM (SELECT *, CASE WHEN r%2 = 0 THEN ' AND ' WHEN r%3 = 0 THEN ' OR ' ELSE '' END AS chk FROM (SELECT *,COUNT(id) OVER(PARTITION BY id ) AS r FROM tc4) df) b)bb