Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

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?
0
pven13
Asked:
pven13
1 Solution
 
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
 
PortletPaulCommented:
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Guy Hengel [angelIII / a3]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 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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now