The best way to do this with stored proc. (SP). (And easysiest)
Without it, it is interesting excersise, so I'll try to solve it out
with one select :) :

To get
Name, Title with no title concated of several columns,
results would be :

Name Title
Leo CEO
Leo CFO
Nik IT Man.
Nik CEO.
It is quite simple, if you can hardcode column names :

select CEO as NAME , 'CEO' as Title FROM MyTable
group by 1
UNION
select CFO AS NAME , 'CFO' AS Title FROM MyTable.
group by 1

Bad is, that output
Leo CEO
Leo CFO
is not an option.We have to concat them; But union will result in two
rows, if there are different results of set.So, we have to make them to
be the same.
Lets say, we have kind of subselect, which gets an argument 'Name', and
returns Title (We will call it function F(NAME) ).As it always returns
same values for
same argument, our sql

select CEO as NAME , F(CEO) as Title FROM MyTable
group by 1
UNION
select CFO AS NAME , F(CFO) AS Title FROM MyTable.
group by 1

should return correct answer. (Union will make disctinct selection)
Problem is with subselect F(NAME).
It has to be kind of several subselescts:
( I'll still name argumnet :NAME)
Because we dont know , where might be name, and we have to get 1 row
anyway, we query rdb$database (in oracle dual) :
select
COALESCE ((SELECT FIRST 1 'CEO' FROM MyTable M1 WHERE
M1.CEO=:NAME),'') ||' '|| COALESCE ((SELECT FIRST 1 'CFO' FROM MyTable
M1 WHERE M1.CFO=:NAME),'')
from
rdb$database.
(Use coalesce to kill nulls from query [in others nvl,nvl2,ifnull]; use
first 1 to avoid multple rows in singletone select )

So, we have our subselect, and we have main sql. As argument :NAME,
should go in first selection MyTable.CEO, in second MyTable.CFO :
select M_MAIN.CEO as NAME ,
(
select
COALESCE ((SELECT FIRST 1 'CEO' FROM MyTable M1 WHERE
M1.CEO=M_MAIN.CEO),'') ||' '|| COALESCE ((SELECT FIRST 1 'CFO' FROM
MyTable M1 WHERE M1.CFO=M_MAIN.CEO),'')
from
rdb$database.

) as Title FROM MyTable M_MAIN
group by 1
UNION
select M_MAIN.CFO AS NAME ,
(
select
COALESCE ((SELECT FIRST 1 'CEO' FROM MyTable M1 WHERE
M1.CEO=M_MAIN.CFO),'') ||' '|| COALESCE ((SELECT FIRST 1 'CFO' FROM
MyTable M1 WHERE M1.CFO=M_MAIN.CFO),'')
from
rdb$database.

)
AS Title
FROM MyTable M_MAIN.
group by 1


Anyway I doubt if it will be fast query :).
And as it uses some FB spec (first, rbd$database,coalesce), you should
find out same functionality in other db.
(Oh, by the way, I havent tried it, so synax might be slightly
incorrect; main purpose is to get the idea.)
Adomas






Kjell Rilbe wrote:

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

--

Pagarbiai / Best Regards,
Adomas Urbanavicius
+37068543336