Aliana Donovan
asked on
Delphi Ado Query To Converts Joined Columns Into Rows Based on Primary Key
I'm using delphi 7 and ado query to connect to an access database with three tables
https://i.stack.imgur.com/cafOE.jpg
All the tables are related by Primary Key, i'm using the following query using ado query to get the three tables in one single row and it works fine :
select
table1.name,table1,amount,
table2.item1,table2.type1, table2.ite m2,table2. type2,
table3.item3,table3.type3, table3.ite m4,table3. type4,
from ((table1
inner join table2.id_data = table1.id_data)
inner join table3.id_data = table1.id_data)
order by table1.id_data
but what i want to achieve is to get each of item field (table2.item1, table2.item2, table3.item3, table3.item4) to be split or convert into rows (records), perhaps using cross tab or unpivot type of query, doing lot of search on the web and i'm still trying to figure out. is there any way to do this kind of query?
https://i.stack.imgur.com/cafOE.jpg
All the tables are related by Primary Key, i'm using the following query using ado query to get the three tables in one single row and it works fine :
select
table1.name,table1,amount,
table2.item1,table2.type1,
table3.item3,table3.type3,
from ((table1
inner join table2.id_data = table1.id_data)
inner join table3.id_data = table1.id_data)
order by table1.id_data
but what i want to achieve is to get each of item field (table2.item1, table2.item2, table3.item3, table3.item4) to be split or convert into rows (records), perhaps using cross tab or unpivot type of query, doing lot of search on the web and i'm still trying to figure out. is there any way to do this kind of query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you using union all is the answer, i just have to add primary key and it works now
and a sample of what you expect as output ?