?
Solved

Compact result from SQL

Posted on 2014-04-07
6
Medium Priority
?
323 Views
Last Modified: 2014-04-07
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
Comment
Question by:pven13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 1

Author Comment

by:pven13
ID: 39985134
The formatting of the result set in my example is not perfect, but this is what I mean:

Result set example
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39985181
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39985208
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39985212
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
 
LVL 1

Author Comment

by:pven13
ID: 39985221
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
 
LVL 1

Author Comment

by:pven13
ID: 39985231
Thank you, Guy Hengel. That works great.
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question