Manually building a DS and/or can coalesce handle this?

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
 
G

Guest

Hello darrel,

I'm not sure whether coalesce will work for you directly in SQL but in
regards to your question about whether you can manually build datasets from
scratch, you can do something like this:
=-=-=-=-=-
DataSet dataSet = new DataSet();
DataTable dataTable = new DataTable();
DataColumn dataColumn = new DataColumn();

// add columns to table
dataTable.Columns.Add(dataColumn);

// add rows to table
DataRow dataRow = dataTable.NewRow();
dataTable.Rows.Add(dataRow);

// add table to dataset
dataSet.Tables.Add(DataTable);
=-=-=-=-=-
 
D

darrel

I'm not sure whether coalesce will work for you directly in SQL but in
regards to your question about whether you can manually build datasets
from
scratch, you can do something like this:

Brians:

THANKS! That'll work. Not quite as elegant, but doable.

I'm having a logic problem, though. Not sure if I can fix it. The problem is
how I build each row. I read each row of the old DS but only add a new row
in the new DS if the person changes. For instance:

bob | co1
bob | co2
bob | co3
sam | co1

My logic is (pseudo code)
--------------------------

dim dr as new dataRow

while rowcount < total rows in oldDS
if this person is different than the last person then
'insert row into newDS
dr("comanies") = companies.tostring
newDS.Tables(0).rows.add(dr)
end if

if this is a new person
add all the other fields
companies = oldDS.tables(0).rows(rowcount)("company").tostring
end if
rowcount = rowcount+1
end while

----------------------------

I hope that makes sense. I'm reading through each record in the oldDS and
create a new row when I get to a new person. However, I don't add the new
row, until I loop through all of the records for that person, appending to
the company string. Only when a new person is encountered do I close off the
row and add it.

The problem is that I can't DIM a new row within an if/then statement. So I
moved it outside, but that means I only have one row, and it errors out once
I loop through and add one row.

Is there a way around this?

-Darrel
 
D

darrel

I'm having a logic problem, though

Got it. Finally realized that I had to do a sub-loob with in my parent
WHILE. Things worked perfectly then. thanks, Brians!

-Darrel
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top