Solved

Compact result from SQL

Posted on 2014-04-07
6
320 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Percentage Formula 7 33
HIghlights of SSIS? 3 42
Migrate SQL 2005 DB to SQL 2016 4 28
Need help with part of sql query for a condition 12 32
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

734 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