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

Comments

Unknown said…
Actually, I would do something like

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

To get rid of the last ','.
Mar 14, 2008, 7:47:00 AM
shlomoid 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).
Nov 9, 2008, 2:14:00 AM
Unknown said…
Code has a bug, it doesn't work
Jun 24, 2009, 5:13:00 PM
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
Apr 8, 2010, 3:55:00 AM
Anonymous said…
I love this kind of fast food. I always order the same dish and my husband always order Generic Viagra to delivery.
Aug 31, 2010, 2:25:00 PM
Post a Comment

Popular posts from this blog

Outlook : "operation failed, object could not be found. " when trying to close a PST.

Image
When you try to close a PST. The error message "operation failed, object could not be found" pop up. After you have tried scanpst and detect and repair. Still won't work. Solution. (a hard way) Start Regedit Go to Key HKCU\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles\Microsoft Outlook Internet Settings Where "Microsoft Outlook Internet Settings" is the Outlook profile name that has problem. (note: Outlook support multiple profile even for same Windows login) In the HKCU\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles\Microsoft Outlook Internet Settings, you will see a bunch of long string sub keys in it Like in the format of GUID, like e.g. 75d1fa940a0d3a4497130516edbde90e Then, start at the very first sub keys of HKCU\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles\Microsoft Outlook Internet Settings. Right click on sub key, Select Find (menu) And then, ...
Read more

How to transfer app and data from old iPhone to new iPhone for model IOS17 and IOS18 above.

It always a headache for non tech savvy users to transfer their old iPhone app and data to new iPhone.  Despite all the effort by Apple with well thought out UX design to make it as intuitive as possible for users to do it themselves. It is still a hair pulling feat to take on. The worse part is that I notice Apple UX do not like to explain anything in written words, they would expect that you follow the flow of UX intuitvely. Many if not majority of iPhone users would have problem doing this easily perhaps their intuition is not the same as Apple employee most of the time. I checked Apple website on how to do it, there is also no clear instruction how to do it. Their instruction is simply - "trust my program process, i will get you there somehow". There is no transparency in the instruction unless you walk to Apple store in your town and talk to Apple employee to guide you. Comparing to Andoid phone, it seems like Apple are not so keen on helping their user to transfer data...
Read more

Troubleshooting Outlook Express (IMAP, POP3, HTTP, NEWS) with the log file

Just happen to know this when trying to solve my friend OE problem. You can log in POP3, IMAP, NEWS, HTTP commands of your Outlook Express into a log file. (note, the password in the log file is encrypted by Microsoft, therefore, it is safe to ask user to send the log file over if you are a IT support) To enable logging. ------------------------- Tools(menu) - Options (menu) - Maintenance(menu) - check on the protocol you need. (POP3, IMAP, NEWS or HTTP) To view the log file -------------------------- Goto path C:\Documents and Settings\YourUserName\Local Settings\Application Data\Identities\{8D32DF8B-D3B8-4783-A0C5-FE37E2FC8659}\Microsoft\Outlook Express\pop3.log (for pop3) imap.log for (imap) and other respectively. P.S. The {8D32DF8B-D3B8-4783-A0C5-FE37E2FC8659} may vary and there could be a few Identities in it. Look for the folder with the structure of *.dbx similar to your Outlook Express email account.
Read more