CODEWITHSUNDEEP
sql
szhep
szhep

Reputation: 13

Merging two unrelated query results into single table - how?

I am trying to merge the results of two separate queries that are run on two different tables of the same DB.

Table 1 is the result of the following query:

SELECT db1.table1.AAA AS Col1, SUM(db1.table1.BBB) AS Col2
    FROM db1.table1
    WHERE cond1 AND cond2
    GROUP BY db1.table1.AAA
    ORDER BY db1.table1.AAA

Result:

Col 1    Col2
ABC      1200
BFK      5405
KVR      1254
LRE      9851
(The list goes on)

Table 2 is the result of another query on another table:

SELECT SUM(db1.table2.CCC), SUM(db1.table2.DDD)
    FROM db1.table2
    WHERE cond3

Result:

CCC      DDD
1325     5428

What I am trying to do is getting a table where this second result is added to the other table, transposed, like so:

Col 1    Col2
ABC      1200
BFK      5405
KVR      1254
LRE      9851
CCC      1325
DDD      5428

I have been experimenting with UNION, PIVOT, INSERT, etc. to no avail, and I am absolutely sure that i don't have a clear view on how this could/should work.

Currently i run both queries one after the other and then manually insert those 4 pieces of data from query2 into the result of query1.

Is there a more "elegant" way, where one SQL query would return the same result?

I am working on this in MS Server Management Studio - and also testing in a small tool written by a colleague (the tool handles xml files with the SQL codes in them(.

Upvotes: 0

Views: 75

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

One method uses UNION ALL:

SELECT t1.AAA AS Col1, SUM(t1.BBB) AS Col2
FROM db1.table1 t1
WHERE cond1 AND cond2
GROUP BY db1.table1.AAA
UNION ALL
SELECT 'CCC', SUM(t2.CCC)
FROM db1.table2 t2
WHERE cond3
UNION ALL
SELECT 'DDD', SUM(t2.DDD)
FROM db1.table2 t2
WHERE cond3;

In SQL Server, you can also express this as:

SELECT t1.AAA AS Col1, SUM(t1.BBB) AS Col2
FROM db1.table1 t1
WHERE cond1 AND cond2
GROUP BY db1.table1.AAA
UNION ALL
SELECT v.col1, v.sumcol
FROM (SELECT SUM(t2.CCC) as ccc, SUM(t2.DDD) as ddd
      FROM db1.table2 t2
      WHERE cond3
     ) t2 CROSS APPLY
     (VALUES ('CCC', t2.ccc), ('DDD', t2.ddd)) v(col1, sumcol)

Upvotes: 2

kant312
kant312

Reputation: 1394

You could use two unions and insert the name of the columns in the select:

SELECT 
    db1.table1.AAA AS Col1, 
    SUM(db1.table1.BBB) AS Col2
FROM 
    db1.table1
WHERE 
    cond1 AND cond2
GROUP BY 
    db1.table1.AAA
ORDER BY 
    db1.table1.AAA

UNION

SELECT 
    'CCC',
    SUM(db1.table2.CCC)
FROM 
    db1.table2
WHERE 
    cond3

UNION

SELECT 
    'DDD',
    SUM(db1.table2.DDD)
FROM 
    db1.table2
WHERE 
    cond3

Upvotes: 0

Related Questions