Combining 2 queries

Question

Post reply

Combining 2 queries

andrew 82282

SSC Veteran

Points: 206
More actions
August 28, 2013 at 3:17 pm

#282560

Hi
I have two queries in SQL linked to excel.
Report 1 shows
Contract A E N P
Income 5000 6000 8000 4000
Costs 4000 7000 6000 3000
Report2 shows
Contract A B E
CustomerA 500 200 400
CustomerB 450 100 200
Is it possible to combine them so I get
Contract A B E N P
Income 5000 6000 8000 4000
Costs 4000 7000 6000 3000
CustomerA 500 200 400
CustomerB 450 100 200
Thanks
Sent from my iPad

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

Something like this perhaps...

-- your data

DECLARE @Report1 TABLE (contract varchar(20), A int, E int, N int, P int);

INSERT @Report1

SELECT 'Income', 5000, 6000, 8000, 4000 UNION

SELECT 'Costs', 4000, 7000, 6000, 3000;

DECLARE @Report2 TABLE (contract varchar(20), A int, B int, E int);

INSERT @Report2

SELECT 'CustomerA', 500, 200, 400 UNION

SELECT 'CustomerB', 450, 100, 200

-- Results

SELECT contract,A,'' AS B,E,N,P

FROM @Report1

UNION ALL

SELECT contract,A,B,E,'' AS N,'' AS P

FROM @Report2

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

andrew 82282 SSC Veteran Points: 206 More actions · Answer 2

Hi

Thanks for the reply, however to do that wouldn't I need to know all the data (of which in reality there is lots)

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

Yes, you need to know all columns and define your query accordingly.

But WHY do you want to display two completely different resultsets into one single resultset?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

andrew 82282 SSC Veteran Points: 206 More actions · Answer 4

HanShi (8/29/2013)
Y
But WHY do you want to display two completely different resultsets into one single resultset?

Although two different result sets (one is costs the other activity) I wanted to creat a report that would show everything for that column (contract) so that we had the income and costs (form one report) then underneath the activity we were doing (from the other report).

HanShi SSC-Dedicated Points: 33506 More actions · Answer 5

In my opinion the easiest solution will be to create the report with two seperated tablixes, each holding a different resultset.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **