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
As an Amazon Associate we earn from qualifying purchases.