Re: SQL - Row Append/Concat

Scott Johnson wrote:

I am looking for a method in SQL to append a column from
multiple rows into a single column of a result set.

If I have a table with the following (simplified example):

AddrID AddrTyp
1 Bill
1 Ship
1 Mail
2 Ship
2 Mail
3 Bill

And want to see the following in an SQL result set:

1 Bill,Mail,Ship
2 Mail,Ship
3 Bill

Is this something that is possible in SQL?

A result set with the ranking of the Type values within each of the ID values, which is then referenced in a recursive query, can be used to generate the various rows. Finally each row that was generated from the highest ranking, are then selected as the rows with [all of] the comma separated values; i.e. rows generated from other than the highest ranking will have only a partial list of values.

I give here some visual representation of how the data is generated.

<code>
:original file data
ID Type
1 Bill
1 Ship
1 Mail
2 Ship
2 Mail
3 Bill

:ranked Types, within each Id value
ID Rank Type
1 1 Bill
1 2 Mail
1 3 Ship
2 1 Mail
2 2 Ship
3 1 Bill

:generated list of Types
ID Rank Type
1 1 Bill
1 2 Bill,Mail
1 3 Bill,Mail,Ship
2 1 Mail
2 2 Mail,Ship
3 1 Bill

:generated list of Types, only of max rank in like ID
ID Rank Type
1 3 Bill,Mail,Ship
2 2 Mail,Ship
3 1 Bill

</code>

The general query for generating the results, but the "ranked Types within each Id value" query is omitted; left as an exercise for the reader, to to someone that either offers up the query or notes that it is not easily effected.

<code>
with
elem_rows (Id, eRank, Type) as
( select ... /* ranking query not given */
)
,elem_list (Id, eNbr, TypeVln) as
( select Id, 1, varchar(Type, 90)
from elem_rows E where eRank=1
union all
select c.Id, c.eNbr+1
,strip(c.Type) concat ',' concat strip(r.Type)
from elem_list c, elem_rows r
where c.eNbr+1 = r.eRank
)
,rank_max (Id, MaxRank) as
( select Id, Max(eRank)
from elem_rows
)
select Id, TypeVln as TypeList
from elem_list L
where eNbr=(select MaxRank
from rank_max M
where L.Id=M.Id)
</code>

The result of the outer SELECT [assuming I got everything correct, and the missing CTE SELECT syntax is added; no system to test]:

<code>
ID TYPELIST
1 Bill,Mail,Ship
2 Mail,Ship
1 Bill
******** End of data ********
</code>

Regards, Chuck