Full Outer Join, Coalesce, and Group By (Oh My!)

Question

I'm going to ask this in two parts, because my logic may be way off, and if so, the syntax doesn't really matter.

1. I have 10 queries. Each query returns month, supplier, and count(some metric). The queries use various tables, joins, etc. Not all month/supplier combinations exist in the output for each query. I would like to combine these into a single data set that can be exported and pivoted on in Excel.

I'd like the output to look like this:

Month | Supplier | Metric1 |Metric2 |..| Metric 10
2018-01 | Supp1 | _value_ | _value_ |...| _value_ |
2018-01 | Supp2 | NULL | _value_ |...| NULL

What is the best / easiest / most efficient way to accomplish this?

2. I've tried various methods to accomplish the above, but I can't seem to get the syntax quite right. I wanted to make a very simple test case and build upon it, but I only have select privileges on the db, so I am unable to test it out. I was able to create a query that at least doesn't result in any squiggly red error lines, but applying the same logic to the bigger problem doesn't work.

This is what I've got:

 create table test1(name varchar(20),credit int);
 insert into test1 (name, credit) values ('Ed',1),('Ann',1),('Jim',1),('Ed',1),('Ann',1);
 create table test2 (name varchar(10), debit int);
 insert into test2 (name, debit) values ('Ann',1),('Sue',1),('Sue',1),('Sue',1);

 select
    coalesce(a.name, b.name) as name,
    cred,
    deb
 from
    (select name, count(credit) as cred
        from test1 
        group by name) a
    full outer join 
    (select name, count(debit) as deb
        from test2
        group by name) b  on 
     a.name =b.name;

Am I headed down the right path?

UPDATE: Based on Gordon's input, I tried this on the first two queries:

select Month, Supp,
    sum(case when which = 1 then metric end) as Exceptions,
    sum(case when which = 2 then metric end) as BackOrders
from (
        (
         select Month, Supp, metric, 1 as which
         from (
                select (convert(char(4),E.PostDateTime,120)+'-'+convert(char(2),E.PostDateTime,101)) as Month, E.TradingPartner as Supp, count(distinct(E.excNum)) as metric
                from db..TrexcMangr E
                where (E.DSHERep in ('AVR','BTB') OR E.ReleasedBy in ('AVR','BTB')) AND year(E.PostDateTime) >= '2018'
              ) a
         )

         union all

        (
         select Month, Supp, metric, 2 as which
         from (
                select (convert(char(4),T.UpdatedDateTime,120)+'-'+convert(char(2),T.UpdatedDateTime,101)) as Month, P.Supplier as Supp, count(*) as metric
                from db1..trordertext T
                    inner join mdid_Tran..trOrderPO P on P.PONum = T.RefNum
                where T.TextType = 'BO' AND (T.CreatedBy in ('AVR','BTB') OR T.UpdatedBy in ('AVR','BTB')) AND year(UpdatedDateTime) >=2018
              ) b
        )
     ) q
group by Month, Supp

... but I'm getting a group by error.

Answer 1

One method uses union all and group by:

select month, supplier,
       sum(case when which = 1 then metric end) as metric_01,
       sum(case when which = 2 then metric end) as metric_02,
       . . .
from ((select Month, Supplier, Metric, 1 as which
       from (<query1>) q
       . . .
      ) union all
      (select Month, Supplier, Metric, 2 as which
       from (<query2>) q
       . . .
      ) union all
      . . .
     ) q
group by month, supplier;