Solved

Compact result from SQL

Posted on 2014-04-07
6
319 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
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 48

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 500 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

749 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