Wednesday, December 05, 2007

MSSQL GROUP_CONCAT

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

5 comments:

Adrien said...

Actually, I would do something like

SELECT Types,
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.