September 23, 2004 at 3:14 am
Dear all,
I have a master and detail table. I wanted to create a join table with the two and transpose the rows in join table to columns. I would like to have a generic tool to determine the distinct rows in the join table and create a table (temp or whatever).
Please help!!
Thanks,
Molla
PS - please see the example below what i want the result to be.
Master table
primary key, name
1, bire
2, molla
3, abeba
4, butu
Detail table
primary key, foreign key (master pk), year, value
a,1,1990,26
b,1,2000,30
c,2,1990,20
d,2,2000,40,
e,2,2001,30
f,3,2001,80
g,4,2004,90
I want the resultant table to be like this
name, 1900, 2000, 2001, 2004-- column name
bire, 26, 30, null, null
molla, 20, 40, 30, null
abeba, null, null, 80, null
butu, null, null, null, 90
Please help....
Thanks
September 23, 2004 at 5:21 am
I would attempt to research how to do that with the reporting tool that you are going to spoonfeed this data to. Most if not ALL reporting tools from Excel, Access, Crystal Reports, BRIO, etc... Offer wizards to perform this pivot table/cross tab report, or whatever you choose to call it with VERY LITTLE effort.
If you still want to proceed down this path you will need to use DYNAMIC SQL to determine the MAX columns and then INSERT your base data into the TEMP table you created with the dynamic SQL and then perform UPDATE statements for each column until ALL the columns are populated. PLEASE NOTE: you will need to be aware that not ALL columns for ALL rows will have data and you will need to account for that or whomever you are passing the data to will.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply