This is using XML feature of the SQL server. (nothing new here)
select Type, RestaurantNames from Restaurant AS A CROSS APPLY
(SELECT RestaurantName + ',' FROM Restaurant AS B WHERE A.Type = B.Type FOR XML PATH('')) D (RestaurantNames) GROUP BY Type, RestaurantNames
This is to get something like below (similar to MYSQL GROUP_CONCAT)
Type |RestaurantNames
----- ---------------
Chinese Food | Ah Yat Abalone, Liang Yah Yong Tau Foo,
Indian Food | Kanna Curry House,
Western Fast Food | Burger King, McDonald
instead of multiple rows.
like
Type |RestaurantName
----- ---------------
Chinese Food |Ah Yat Abalone
Chinese Food |Liang Yah Yong Tau Foo
Indian Food |Kanna Curry House
Subscribe to:
Post Comments (Atom)
3 comments:
Actually, I would do something like
SELECT Types,
SUBSTRING(RestaurantNames, 1, LEN(RestaurantNames)-1) as RestaurantNames
To get rid of the last ','.
For whomever it is relevant to, I've published a similar yet alternative way to do this on my blog. (I'm also using the xml trick, but without cross apply).
Code has a bug, it doesn't work
Post a Comment