I have a table like this:

If I have a "Result" in September I want to use that, if not I want to use the Forecast for September and group by Account number.

Does anyone know how to produce something like the below?

Sometimes there isn't a result row.

I've tried different case statements and coalesce but without successful results.

Thanks!

CodePudding user response：

If i get it right from question, it should work.

select m.Account, 
case when m.Sept is null then d.Sept else m.Sept End Sept,
case when m.Oct is null then d.Oct else m.Oct End Oct,
case when m.Nov is null then d.Nov else m.Nov End Nov
from 
table_name m
inner join table_name d on m.Account = d.Account and d.Source = 'Forecast'
where m.Source = 'Result' 

CodePudding user response：

As two types of source exists in table so use subquery which will return account wise result and forecasting value. Using COALESCE() where placing result value at first then forecast value in second.

-- SQL Server
SELECT t.account, COALESCE(t.r_Sep, t.f_Sep) "Sep"
     , COALESCE(t.r_Oct, t.f_Oct) "Oct"
     , COALESCE(t.r_Nov, t.f_Nov) "Nov"
FROM (SELECT account
           , MAX(CASE WHEN source = 'Result' THEN Sep END) "r_Sep"
           , MAX(CASE WHEN source = 'Forecast' THEN Sep END) "f_Sep"
           , MAX(CASE WHEN source = 'Result' THEN Oct END) "r_Oct"
           , MAX(CASE WHEN source = 'Forecast' THEN Oct END) "f_Oct"
           , MAX(CASE WHEN source = 'Result' THEN Nov END) "r_Nov"
           , MAX(CASE WHEN source = 'Forecast' THEN Nov END) "f_Nov"
      FROM tbl
      GROUP BY account) t

Another way when using COALESCE() WHERE max value of result is placed first then max value of forecast.

-- SQL Server
SELECT account
     , COALESCE(MAX(CASE WHEN source = 'Result' THEN Sep END), MAX(CASE WHEN source = 'Forecast' THEN Sep END)) "Sep"
     , COALESCE(MAX(CASE WHEN source = 'Result' THEN Oct END), MAX(CASE WHEN source = 'Forecast' THEN Oct END)) "Sep"
     , COALESCE(MAX(CASE WHEN source = 'Result' THEN Nov END), MAX(CASE WHEN source = 'Forecast' THEN Nov END)) "Sep"
FROM tbl
GROUP BY account

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=5e96ed33a6d9265739c3b935e5f6824a

N.B.: you can get "Warning: Null value is eliminated by an aggregate or other SET operation" message just ignore it. It's not an error