Re: SQL - Row Append/Concat

[CRPence <CRPbottle@xxxxxxxxx>]

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