Vendor agnostic SQL to concatenate field values across records

I have the following DB Schema :-

Data is ...

Location Table
1. New York
2. London
3. Tokyo
4. Melbourne

OtherNames Table (aka Aliases)

1. NYC
1. New York City
4. Home
3. Foo
3. PewPew

What I'm trying to do, as SQL, is get the following results :-

ID, Name, Name + Aliases

开发者_如何转开发

eg.

1 | New York | new york nyc new york city
2 | London | NULL
3 | Tokyo | tokyo foo pewpew
4 | Melbourne | melbourne home

I'm not sure how to get that LAST column.

It's like I want to have a SubQuery which COALESCE's the OtherName.Name field, per Location row... ?

It's related to a previous question I have .. but my previous question doesn't give me the proper results I was after (I didn't ask the right question, before :P)

NOTE: I'm after a TSQL / Non server specific answer. So please don't suggest GROUP_CONCAT();

---

SQL isn't suited to this kind of operation (1NF violation and all that), therefore the various workarounds in SQL will be vendor-specific. If you want something vendor-independent then use something that will consume vanilla SQL (rather than generate it) e.g. a report writer or 3GL application ;)

---

If you're using SQL Server 2005 onwards, I personally like the XPATH approach