Hi,

In generic SQL (this is not for Firebird in particular), how would you
solve the following problem? I assume a stored procedure could do it,
but can it be done without one?

I have a table with a set of columns containing names, e.g. "chairman",
"CEO", "CFO", "IT manager". For each particular record, the same name
might appear in multiple columns, and some columns might be null. (I
know this should/could be normalized, but this is how it is and
normalization is not an option right now.)

I would like to produce a result set where there's one row per unique
name per row, and where a column contains a comma separated list of the
column names where that name appears in the original table.

I can hardcode the column name strings for the output into the SQL, so
there's no need to query metadata.


EXAMPLE
=======

Row# Chairman CEO CFO IT manager
---- -------- --- --- ----------
1 NULL Leo Leo Joe
2 Nik Nik Max Leo

The above data should result in this result set:

Row# Name Titles
---- ---- -------------
1 Leo CEO, CFO
1 Joe IT manager
2 Nik Chairman, CEO
2 Max CFO
2 Leo IT manager


Any suggestions?

Thanks,
Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64