Link to home
Start Free TrialLog in
Avatar of Aliana Donovan
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.item2,table2.type2,
table3.item3,table3.type3,table3.item4,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?
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
do you have a sample of the input rows
and a sample of what you expect as output ?
Avatar of Aliana Donovan
Aliana Donovan

ASKER

Thank you using union all is the answer, i just have to add primary key and it works now