Vendor agnostic SQL to concatenate field values across records

Question

Hi folks,

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();

Answer 1

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

Answer 2

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 ;)