D
darrel
I have three tables making a many-to-many relationship:
Companies
CoID | CoName
Link
CoID | InsID
Insurers
InsID | InsName
Normally, I'd do a few joins and end up with a dataset that creates a new
record for each Co/Ins pair. I'd then manually loop through the DS pulling
out the unique Ins and formatting the data so only one Co shows up but with
all of the applicable Ins listed.
I now need to do the opposite...I need a dataset that only has one record
for each Co and puts all of the Ins related to it into one Insurer field for
that record.
I've been told that Coalesce can maybe do this directly in SQL, but the
examples I've seen just take one table and coalesce a few fields together,
rather than doing any sort of M2M join.
Can coalesce work for this?
If not, I was just going to do my normal query, and then loop through the
records as I usually do. However, Instead of writing out HTML, I wanted to
write it to a new DS. However, I'm not having any luck finding examples of
how to manually write to a DS to build it from scratch (or maybe I can't
even do that?)
I need a DS in the end as I'm passing this to a function that takes the DS
and then spits out an XLS file.
-Darrel
Companies
CoID | CoName
Link
CoID | InsID
Insurers
InsID | InsName
Normally, I'd do a few joins and end up with a dataset that creates a new
record for each Co/Ins pair. I'd then manually loop through the DS pulling
out the unique Ins and formatting the data so only one Co shows up but with
all of the applicable Ins listed.
I now need to do the opposite...I need a dataset that only has one record
for each Co and puts all of the Ins related to it into one Insurer field for
that record.
I've been told that Coalesce can maybe do this directly in SQL, but the
examples I've seen just take one table and coalesce a few fields together,
rather than doing any sort of M2M join.
Can coalesce work for this?
If not, I was just going to do my normal query, and then loop through the
records as I usually do. However, Instead of writing out HTML, I wanted to
write it to a new DS. However, I'm not having any luck finding examples of
how to manually write to a DS to build it from scratch (or maybe I can't
even do that?)
I need a DS in the end as I'm passing this to a function that takes the DS
and then spits out an XLS file.
-Darrel