Peter van der Ven
asked on
Compact result from SQL
I am looking for a way to combine information from several tables into one query result, minimizing the number of records in the result set.
Example:
The query result I am looking for is something like this:
9999 9999 123 789 1
9999 124 790 2
9999 791 3
9999 4
The way in which the elements are combined is not important. What matters is that every element is listed only once in the result set (except for the AgreementId in column 1), that the number of lines is determined by the element with the maximum number of occurs (in this case BP with 4 different values) and that the set is produced using only SQL, so no scripting.
Anybody any suggestions on how to tackle this one?
Example:
create table Agreement (AgreementId int)
create table Loan (AgreementId int, LoanId int)
create table REObject (AgreementId int, REObjectId int)
create table BP (AgreementId int, BPId int)
insert into Agreement values(9999)
insert into Loan values(9999, 123)
insert into Loan values(9999, 124)
insert into REObject values(9999, 789)
insert into REObject values(9999, 790)
insert into REObject values(9999, 791)
insert into BP values(9999, 1)
insert into BP values(9999, 2)
insert into BP values(9999, 3)
insert into BP values(9999, 4)
The query result I am looking for is something like this:
9999 9999 123 789 1
9999 124 790 2
9999 791 3
9999 4
The way in which the elements are combined is not important. What matters is that every element is listed only once in the result set (except for the AgreementId in column 1), that the number of lines is determined by the element with the maximum number of occurs (in this case BP with 4 different values) and that the set is produced using only SQL, so no scripting.
Anybody any suggestions on how to tackle this one?
with current table sturucture it is not possible to get above result
as ther no relationship between Loan , REobject and BP table
select Distinct A.AgreementId Agreement,
L.AgreementId ,
L.LoanId ,
R.REObjectId ,
BPId from Agreement A
inner join Loan L on L.AgreementId = A.AgreementId
inner join REObject R on R.AgreementId = A.AgreementId
inner join BP B on B.AgreementId = A.AgreementId
as ther no relationship between Loan , REobject and BP table
select Distinct A.AgreementId Agreement,
L.AgreementId ,
L.LoanId ,
R.REObjectId ,
BPId from Agreement A
inner join Loan L on L.AgreementId = A.AgreementId
inner join REObject R on R.AgreementId = A.AgreementId
inner join BP B on B.AgreementId = A.AgreementId
May I ask why you want this particular output style?
SQL will repeat values; the opposite of what you have asked for. It will also multiply the rows when making joins, and "select distinct" is a "row operator" it assess the entire row when eveluating if a row is unique or not. Here it does not help.
e.g.
SQL will repeat values; the opposite of what you have asked for. It will also multiply the rows when making joins, and "select distinct" is a "row operator" it assess the entire row when eveluating if a row is unique or not. Here it does not help.
e.g.
**Query 1**:
select Distinct A.AgreementId Agreement,
L.AgreementId ,
L.LoanId ,
R.REObjectId ,
BPId from Agreement A
inner join Loan L on L.AgreementId = A.AgreementId
inner join REObject R on R.AgreementId = A.AgreementId
inner join BP B on B.AgreementId = A.AgreementId
**[Results][2]**:
| AGREEMENT | AGREEMENTID | LOANID | REOBJECTID | BPID |
|-----------|-------------|--------|------------|------|
| 9999 | 9999 | 123 | 789 | 1 |
| 9999 | 9999 | 123 | 789 | 2 |
| 9999 | 9999 | 123 | 789 | 3 |
| 9999 | 9999 | 123 | 789 | 4 |
| 9999 | 9999 | 123 | 790 | 1 |
| 9999 | 9999 | 123 | 790 | 2 |
| 9999 | 9999 | 123 | 790 | 3 |
| 9999 | 9999 | 123 | 790 | 4 |
| 9999 | 9999 | 123 | 791 | 1 |
| 9999 | 9999 | 123 | 791 | 2 |
| 9999 | 9999 | 123 | 791 | 3 |
| 9999 | 9999 | 123 | 791 | 4 |
| 9999 | 9999 | 124 | 789 | 1 |
| 9999 | 9999 | 124 | 789 | 2 |
| 9999 | 9999 | 124 | 789 | 3 |
| 9999 | 9999 | 124 | 789 | 4 |
| 9999 | 9999 | 124 | 790 | 1 |
| 9999 | 9999 | 124 | 790 | 2 |
| 9999 | 9999 | 124 | 790 | 3 |
| 9999 | 9999 | 124 | 790 | 4 |
| 9999 | 9999 | 124 | 791 | 1 |
| 9999 | 9999 | 124 | 791 | 2 |
| 9999 | 9999 | 124 | 791 | 3 |
| 9999 | 9999 | 124 | 791 | 4 |
[1]: http://sqlfiddle.com/#!3/bd5ec/1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The reason why I want this particular output is that it is used as a load file in a data migration project. We have to add the information in the load file to the target system. The more lines the load file contains (or the more repeating occurences of the same information) the longer the load takes.
I am aware of the fact that SQL will repeat the values. I was wondering if someone knows a way to avoid it.
I am aware of the fact that SQL will repeat the values. I was wondering if someone knows a way to avoid it.
ASKER
Thank you, Guy Hengel. That works great.
ASKER