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.

Type |RestaurantName
----- ---------------
Chinese Food |Ah Yat Abalone
Chinese Food |Liang Yah Yong Tau Foo
Indian Food |Kanna Curry House


Adrien said…
Actually, I would do something like

SUBSTRING(RestaurantNames, 1, LEN(RestaurantNames)-1) as RestaurantNames

To get rid of the last ','.
Shlomo Priymak said…
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).
Victoria said…
Code has a bug, it doesn't work
Anthony Yio said…
It has been years I wrote this and so today I need referencing it again. Hmm, I guess it prove me right that I wrote this in the past and now, it is helping myself too.

About the bug, not sure of what Victoria problem is. However, becareful with JOINNED tables with alias.If you having syntax error with 'AS'. You need to bracket the whole thing including the SELECT statement then only you alias it. Or better, just use View to give clearer picture. Basically, the "AS" only work with table or view. Bracketing (SELECT * TABLE) is similar to a temp table.

Also, thanks Adrien for the removing comma tip thou i am doing it differently.

Apart from that, the GROUP BY is actually not necessary, Consider removing it.

The columns on top, you can add more such as A.column1, A.column2, RestaurantNames because you are doing CROSS APPLY and not GROUPING with B.

Hope this is helpful to MSSQL DBA swearing for not having MYSQL GROUP_CONCAT in MSSQL
Josh said…
I love this kind of fast food. I always order the same dish and my husband always order Generic Viagra to delivery.

Popular posts from this blog

Clearcase check in/uncheck out all the checked out files script

Duplicating a local copy of project from TFS and making web software client factory to work