Flatten a One-to-Many Relationship

Sorry for the 'beginner' question,

Does anyone have a good technique for creating a data set that flattens a one-to-many relationship? I have created data sets that represent each of the involved tables, but haven't been able to figure how create the join data set that will build a single table from both sides of the one-to-many.

I assume I will need to create some sort of function to pull them together, but haven't been able to find any examples on DevShare or in the help. BTW, my data source is a MS SQL Server database.

Thanks in advance,

Rob

Find more posts tagged with

Comments

mwilliams

Hi Rob,

Can you give me an example of what your data looks like in both dataSets that you're wanting to join?

rtruban

Michael,

Thanks you very much for replying, I will try and explain a little better?

Using the classic PUBS database as an example?. There is a one to many relationship between the ?author? table and ?titles? table. The author table has ?author name?, etc... The title table has ?title name?, ?publisher?, ?published date? etc.

Author Table:
FName
LName
Email Address
?

Title Table
Name
Description
Publisher
Publish Date
?

What I need to do is create a table where each record has the Author name, and column that contains the name of each title he has authored:

Author Name, Title1 Name, Title2 Name, Title3 Name?

Does that make sense?

Thanks in advance,

Rob

mwilliams

Rob,

Sorry for the delayed response on this. So, you would like to get a display like:

Author 1, A1_Book1, A1_Book2
Author 2, A2_Book1, A2_Book2, A2_Book3
...
...
etc.

Or with the classic models database, something like:

Customer 1, C1_Order1, C1_Order2
Customer 2, C2_Order1, C2_Order2
...
...
etc.

rtruban

Michael, 
 
Yes, I believe that is the equivalent of what I am trying to do. The article I provided a link to in my JavaScript thread explains the problem clearer than I did. I am trying to achieve exactly what he does in the article, but of course using the features that BIRT provides to accomplish solving the problem. 
 
Here is the link again: 
<a class='bbc_url' href='http://aspadvice.com/blogs/plitwin/archive/2005/12/23/14445.aspx'>Paul Litwin's Blog : Flattening Data into a List in SQL Server</a> 
 
I bought the books thinking it would be a common problem with a simple solution. So far nothing is jumping out at me! 
 
Thanks for help, 
 
Rob

rtruban

I found another article describing a solution to this as well: 
<a class='bbc_url' href='http://ryanfarley.com/blog/archive/2005/02/17/1712.aspx'>Flattening Out Data with One of the Coolest SQL Tricks Ever</a> 
 
I tried to emulate this solution but this doesn't work in the query of a dataset: 
 
declare ordersList varchar(1000) 
set ordersList = '' 
 
select ordersList=@ordersList + CLASSICMODELS.ORDERS.ORDERNUMBER + "," 
from CLASSICMODELS.ORDERS, CLASSICMODELS.CUSTOMERS 
where CLASSICMODELS.ORDERS.CUSTOMERNUMBER=CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER 
 
return ordersList 
 
Maybe there is a way to do this with the scripting? 
 
Thanks 
 
Rob