Combining query outputs to make one summary table in SQL Server-CodePudding

Q1 outputs

DogCnt
101

Q2 outputs

CatCnt
6

Q3 outputs

BirdCnt
21

How can I combine both query outputs in to generate this output?

Animal  Cnt
Dog     101
Cat       6
Bird     21

CodePudding user response：

You can combine them with UNION ALL:

SELECT Animal = 'Dog', Cnt = DogCnt FROM (<Q1>) AS Q1
UNION ALL
SELECT Animal = 'Cat', CatCnt FROM (<Q2>) AS Q2
UNION ALL
SELECT Animal = 'Bird', BirdCnt FROM (<Q3>) AS Q3;

But if all these source queries are originally coming from the same table, there is absolutely a more efficient way to do that (for specifics you'll need to provide more specifics, like what is the table structure and how are you getting those counts).

CodePudding user response：

You can use multiple CTEs :

;WITH Q1 (
    <query here>
), Q2 (
    <query here>
), Q3 (
    <query here>
)

select 'dog' as animal, DogCnt
from q1 
union all
select 'Cat' as animal, CatCnt
from q2
union all
select 'Bird' as animal, BirdCnt
from q3;