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:
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)

Open in new window


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?
LVL 1
pven13Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
actually, this IS possible, using intermediate inline queries to build up row numbers:
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)

set nocount on
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)

go

; with Loans as ( select AgreementId, LoanId
	, row_number() over (partition by AgreementId order by loanid) rn 
		from loan )
, REObjects as ( select AgreementId, REObjectId
	, row_number() over (partition by AgreementId  order by REObjectId) rn 
		from REObject )
, BPs as ( select AgreementId, BPid
	, row_number() over (partition by AgreementId order by BPID) rn 
		from BP )
 , rn as ( select AgreementId, rn from loans		
			union 
		   select AgreementId , rn from REObjects
		   union
		   select AgreementId , rn from BPs
		)
select a.AgreementId
  , l.LoanId 
  , r.REObjectId 
  , b.BPid 
  from Agreement a
  join rn on rn.AgreementId = a.AgreementId
  left join Loans l on l.AgreementId = a.AgreementId  and l.rn = rn.rn
  left join REObjects r on r.AgreementId = a.AgreementId and r.rn = rn.rn
  left join BPs b on b.AgreementId = a.AgreementId  and b.rn = rn.rn

go

drop table Agreement
drop table Loan
drop table REObject
drop table BP

Open in new window

0
 
pven13Author Commented:
The formatting of the result set in my example is not perfect, but this is what I mean:

Result set example
0
 
Pratima PharandeCommented:
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
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
PortletPaulfreelancerCommented:
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.
**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

Open in new window

0
 
pven13Author Commented:
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.
0
 
pven13Author Commented:
Thank you, Guy Hengel. That works great.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.