Add different queries results together

Question

Thanks in advance!

Is there a good and fast way to put several queries' results together(like put in a table) from  a stored procedure? See,  query  Q1 returns 5 rows data, Q2 returns 5 rows too but with different numbers, Q3 returns 5 rows with different values... and we need to put the 3 column's data into a table( or make it look like a report)

Any ideas?

Answer 1

You could use UNION or UNION ALL to union the data sets, then insert the whole result set into your table to manipulate further...

For example:

INSERT INTO Table

SELECT Col1, Col2 FROM Q1

UNION ALL

SELECT Col1, Col2 FROM Q2

More on UNION: http://msdn.microsoft.com/en-us/library/ms180026(v=sql.90).aspx

---

Zach Stagers - http://www.scratchbox.co.uk

Answer 2

thanks very much for your help.

Tried Union all, the data goes to a different direction. The following format is what we are looking for:

Q1Res  Q2Res    Q3Res

Q1r1     Q2r1     Q3r1

Q1r2     Q2r2     Q3r2

Q1r3     Q2r3     Q3r3

Q1r4     Q2r4     Q3r4

Q1r5     Q2r5     Q3r5

Thanks

Answer 3

Looks like you want to use PIVOT (columns to rows), possibly in addition to the UNION.

http://msdn.microsoft.com/en-us/library/ms177410.aspx

Answer 4

You may want to JOIN results of the queries based on some common field(s).

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

How about splitting each query into a CTE, giving them each a ROW_NUMBER column to join on such as follows:

;WITH Q1Res AS 
		(
		 SELECT 'Q1r1' Res UNION
		 SELECT 'Q1r2' Res UNION
		 SELECT 'Q1r3' Res UNION
		 SELECT 'Q1r4' Res UNION
		 SELECT 'Q1r5' Res
		),
	  Q2Res AS 
		(
		 SELECT 'Q2r1' Res UNION
		 SELECT 'Q2r2' Res UNION
		 SELECT 'Q2r3' Res UNION
		 SELECT 'Q2r4' Res UNION
		 SELECT 'Q2r5' Res
		),
	  Q3Res AS 
		(
		 SELECT 'Q3r1' Res UNION
		 SELECT 'Q3r2' Res UNION
		 SELECT 'Q3r3' Res UNION
		 SELECT 'Q3r4' Res UNION
		 SELECT 'Q3r5' Res
		)	
		
SELECT r1.Res,
	   r2.Res,
	   r3.Res

FROM (
      SELECT Res, ROW_NUMBER() OVER(ORDER BY Res) [ranker]
	  FROM Q1Res 
     ) r1
	 FULL OUTER JOIN 
	 (
      SELECT Res, ROW_NUMBER() OVER(ORDER BY Res) [ranker]
	  FROM Q2Res 
     ) r2 ON r1.ranker = r2.ranker
     
	 FULL OUTER JOIN 
	 (
      SELECT Res, ROW_NUMBER() OVER(ORDER BY Res) [ranker]
	  FROM Q3Res
     ) r3 ON r1.ranker = r3.ranker

---

Zach Stagers - http://www.scratchbox.co.uk

Answer 6

I hope there is a strong reason you would want to do this. I might be wrong, but I do not see a logical association in the result. I mean the results, if are coming from 3 separate queries, you might want to really think hard before, doing this, as the results (the rows in the results) may not be associated. However, having said that, if you still would want to do it, here's how you can do it:

;WITH CTE1 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY Col1) RwNum,Col1 FROM
(
SELECT 'Q1R1' AS Col1 UNION ALL
SELECT 'Q1R2' AS Col1 UNION ALL
SELECT 'Q1R3' AS Col1 UNION ALL
SELECT 'Q1R4' AS Col1 UNION ALL
SELECT 'Q1R5' AS Col1 UNION ALL
SELECT 'Q1R6' 
)A
),
CTE2 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY Col1)RwNum,Col1 FROM
(
SELECT 'Q2R1' AS Col1 UNION ALL
SELECT 'Q2R2' AS Col1 UNION ALL
SELECT 'Q2R3' AS Col1 UNION ALL
SELECT 'Q2R4' AS Col1 UNION ALL
SELECT 'Q2R5' AS Col1 UNION ALL
SELECT 'Q2R6' 
)B
),
CTE3 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY Col1)RwNum,Col1 FROM
(
SELECT 'Q3R1' AS Col1 UNION ALL
SELECT 'Q3R2' AS Col1 UNION ALL
SELECT 'Q3R3' AS Col1 UNION ALL
SELECT 'Q3R4' AS Col1 UNION ALL
SELECT 'Q3R5' AS Col1 UNION ALL
SELECT 'Q3R6' 
)C
)

SELECT C1.Col1,C2.Col1,C3.Col1 FROM 
CTE3 C3 INNER JOIN CTE2 C2 ON C3.RwNum =C2.RwNum 
INNER JOIN CTE1 C1 ON C1.RwNum = C2.RwNum

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

Answer 7

yes, we need this because we would like to make the results from serveral queries look like a report.

Still strugging "with" , and what if all queries are complecated, like:

select colA, colB, count(*) from TA

Where myDate >'12/11/2011' and myDate <'2/22/2012'

and colC='textC' and colD='textD'

group by colA, colB

with rollup

All your help is apprecaited!

Answer 8

Hi 

If Number of Rows in each query remain same then you can do this with following example

Declare @Tbl1 Table(Phone Varchar(30),PType Tinyint,RowNo int)
Declare @Tbl2 Table(Phone Varchar(30),PType Tinyint,RowNo int)
Declare @Tbl Table(Phone Varchar(30),PType Tinyint)
Insert into @Tbl Values('234-334-444',0),('[phone]',0),('343534343',0),('56454545',0),
('234-334-222',1),('[phone]',1),('2342424324',1),('456646546',1)

Insert into @Tbl1 
select *,ROW_NUMBER() Over (Order by Phone) from @Tbl where PType=0

Insert into @Tbl2 
select *,ROW_NUMBER() Over (Order by Phone) from @Tbl where PType=1

Select * from @Tbl1 a
Join @Tbl2 b on a.RowNo=b.RowNo

---

Mark as Answer If Reply Was Helpful
Thanks
Kuldeep Bisht
Technical Lead @ Simplion Technologies
Blog : www.dbsimplified.com

Answer 9

Thanks.
Still testing, but got another question:
now the query data from the query
select colA, colB, count(*) from TA
 
Where myDate >'12/11/2011' and myDate <'2/22/2012'
 
and colC='textC' and colD='textD'

group by colA, colB
 
with rollup
looks like:
product  location mycount
ProA       locA   111
ProA       locB   222
ProA       locC   333
ProA       NULL   666

and we need
ProA      666  
locA      111 
locB      222  
locC      333

Any ideas? Thanks very much!

Answer 10

Hi 

You can use IsNULL

Declare @tab Table(Prod Varchar(10),Loc Varchar(20),mycount int)
Insert into @tab values
('ProA'       ,'locA'   ,111),
('ProA'       ,'locB'   ,222),
('ProA'       ,'locC'   ,333),
('ProA'       ,NULL   ,666)
Select * from @tab

Select ISNULL(Loc,Prod) as Loc,Mycount from @tab

---

Mark as Answer If Reply Was Helpful
Thanks
Kuldeep Bisht
Technical Lead @ Simplion Technologies
Blog : www.dbsimplified.com

Answer 11

Yes, it works fine, only needs a little bit more work. Thanks very much for your great help!!

Select ISNULL(Loc,Prod) as Loc,Mycount from @tab,  got result:

locA      111 
locB      222
locC      333

ProA      666 

Is that possible to have this:

ProA      666  
locA      111 
locB      222  
locC      333

ProB     666

locA      111 
locB      222
locC      333

total    1332

 

Answer 12

Hi 

try Following

Declare @tab Table(Prod Varchar(10),Loc Varchar(20),mycount int)
Insert into @tab values
('ProA'       ,'locA'   ,111),
('ProA'       ,'locB'   ,222),
('ProA'       ,'locC'   ,333),
('ProA'       ,NULL   ,666)

Select Isnull(ISNULL(Loc,Prod),'Total') as Loc,SUM(MyCount) as MyCount from @tab
Group by Rollup(ISNULL(Loc,Prod))
Order by ISNULL(Loc,Prod) desc

---

Mark as Answer If Reply Was Helpful
Thanks
Kuldeep Bisht
Technical Lead @ Simplion Technologies
Blog : www.dbsimplified.com

Answer 13

Tested Select Isnull(ISNULL(Loc,Prod),'Total') as Loc,SUM(MyCount) as MyCount from @tab
Group by Rollup(ISNULL(Loc,Prod))
Order by ISNULL(Loc,Prod) desc, got the result:

Loc      MYCount

loc1     num1

loc2    num2

Product3 num3

loc3    num4

Product1 num5

Total num6

Total num7

Answer 14

Hi Can you post your query and data, because below query works find for me.

Declare @tab Table(Prod Varchar(10),Loc Varchar(20),mycount int)
Insert into @tab values
('ProA'       ,'loc1'   ,45055),
('ProA'       ,'loc2'   ,42671),
('Product3'       ,null   ,59970),
('Product1'       ,NULL   ,10386),
('Product1'       ,'loc3'   ,45472)

Select Isnull(ISNULL(Loc,Prod),'Total') as Loc,SUM(MyCount) as MyCount from @tab
Group by Rollup(ISNULL(Loc,Prod))
Order by ISNULL(Loc,Prod) desc

---

Mark as Answer If Reply Was Helpful
Thanks
Kuldeep Bisht
Technical Lead @ Simplion Technologies
Blog : www.dbsimplified.com

Answer 15

Try:

ORDER BY Case when Loc IS NULL then 1 else 2 end, isnull(Prod, Loc) 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 16

Thanks.

Got an error:

invalid in the order by clause because it is not contained in either an aggregate function or the GROUP BY clause.

Is there a way to get the idea results by editing the first query?

required results:

ProA      666  
locA      111 
locB      222  
locC      333

ProB     666

locA      111 
locB      222
locC      333

total    1332

original query:

select colA, colB, count(*) from TA

Where myDate >'12/11/2011' and myDate <'2/22/2012'

and colC='textC' and colD='textD'

group by colA, colB

with rollup

Answer 17

select coalesce(colB, colA,'total') count(*) from TA
Where myDate >'12/11/2011' and myDate <'2/22/2012'
and colC='textC' and colD='textD'

group by colA, colB  order by colA, colB

Result:

total    1332

ProA      666  
locA      111 
locB      222  
locC      333

ProB     666

locA      111 
locB      222
locC      333

The only thing is to put total 1332 to the end of the result, any ideas?

Another question, I have two queries with same results( just different numbers), is there a way to merge the two query results into one "Table" ? like:

total    1332     12

ProA      666     6
locA      111     1
locB      222     2
locC      333     3

ProB     666      6

locA      111      1
locB      222      2
locC      333      3

Answer 18

any ideas? Thanks.

Answer 19

You Need to add Order by Clause

Order by Coalesce(Colb,Cola) Desc

For joining To Different Result if they have same no. of Rows, the idea is (Put them into different table and join them based on RowNo, if you dont want to use Table variable then you can use CTE)

Declare @Tbl1 Table(Phone Varchar(30),PType Tinyint,RowNo int)
Declare @Tbl2 Table(Phone Varchar(30),PType Tinyint,RowNo int)
Declare @Tbl Table(Phone Varchar(30),PType Tinyint)
Insert into @Tbl Values('234-334-444',0),('[phone]',0),('343534343',0),('56454545',0),
('234-334-222',1),('[phone]',1),('2342424324',1),('456646546',1)

Insert into @Tbl1 
select *,ROW_NUMBER() Over (Order by Phone) from @Tbl where PType=0

Insert into @Tbl2 
select *,ROW_NUMBER() Over (Order by Phone) from @Tbl where PType=1

Select * from @Tbl1 a
Join @Tbl2 b on a.RowNo=b.RowNo

---

Mark as Answer If Reply Was Helpful
Thanks
Kuldeep Bisht
Technical Lead @ Simplion Technologies
Blog : www.dbsimplified.com

Answer 20

Thanks very much for your kind help!

Tried:Order by Coalesce(Colb,Cola) Desc, it did switch the total to the bottom, but the rest sequence is different, the requested format is as below:

ProA      666     6
locA      111     1
locB      222     2
locC      333     3

ProB     666      6

locA      111      1
locB      222      2
locC      333      3

total    1332     12

For storing retrieved data from tables, table variable has better performance than #temp table, is that right? or any other idea for it?