Re: SQL - Row Append/Concat

I'm not sure a "recursive common table expression" (google for it)
would work or not.

Seems to me that this is more like a pivot table requirement. Only
catch with treating it as such is how many AddrTyp are there? If a
small finite number, like your 3 in the example then doing the
following will work

with Addrs as (select distinct AddrID from myTable)
, pivot as( select a.AddrID,
max(case b.AddrTyp
when 'Bill' then 'Bill'
else NULL
end) as AddrType1,
max(case c.AddrTyp
when 'Mail' then 'Mail'
else NULL
end) as AddrType2,
max(case d.AddrTyp
when 'Ship' then 'Ship'
else NULL
end) as AddrType3
from Addrs A left outer join myTable B on a.AddrID = b.AddrID
left outer join myTable C on
a.AddrID = c.AddrID
left outer join myTable D on
a.AddrID = d.AddrID
group by a.AddrID
)
select AddrID,
trim(trailing ',' from (coalesce((AddrType1 concat ','),'')
concat coalesce((AddrType2
concat ','),'')
concat coalesce((AddrType3
concat ','),'')
)
) as AddTypeList
from pivot
order by a.AddrID


For each additional distinct AddrTyp code, you need to add another
- case statement
- left outer join
- concat coalesce line.

HTH,

Charles Wilt




On Tue, Sep 9, 2008 at 8:14 AM, <Michael_Schutte@xxxxxxxxxxxx> wrote:

Yes, you would need to look into the manual for a recursive SQL statement.
I've done them.


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.

Bob Evans NEW! Farmhouse Philly Knife & Fork Sandwich: It's No Ordinary
Sandwich!
Try all four Knife & Fork Sandwiches, starting at 5.99.
For more information, visit www.NoOrdinarySandwich.com


midrange-l-bounces@xxxxxxxxxxxx wrote on 09/09/2008 06:01:24 AM:

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?


-- Scott


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.