How to count multiple columns in SQL (Oracle) with criteria?

I’m working on SMS-Gateway that holds multiple charged SMS-services with different numbers,
each SMS sent to the customer has 4 status as below (forwarded, delivered, expired,delivery failed)

Now I have the below first_table for the charging-system with the below details (TABLE-A)

and below (TABLE-B) which contain the status of each sent SMS with its ID

Below is my expected final result to forecast the details for each sms-service :

At first I thought it was easy all I need is just to use COUNT(Case when ...)
but in my case I have thousands of SMS-numbers(services) so if I use this approach it will be like that:-

COUNT(CASE WHEN a.SMS_SHORT_CODE='1111' AND B.STATUS='forwarded' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='1111' AND B.STATUS='delivered' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='1111' AND B.STATUS='expired' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='1111' AND B.STATUS='delivery failed' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='5000' AND B.STATUS='forwarded' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='5000' AND B.STATUS='delivered' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='5000' AND B.STATUS='expired' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='5000' AND B.STATUS='delivery failed' )
...
...
...
...
...
...
...

The above approach not practical when you have many services also noting that CASE can handle only 250 conditions?

So what is the best approach to do left outer join for (Table A) on (Table B) using the SMS-ID and count each SMS-status and forecast it as below?

Answer

I would suggest conditional aggregation:

select b.SMS_SHORT_CODE,
       sum(case when status = 'forwaded' then 1 else 0 end) as count_of_forwaded,
       sum(case when status = 'delivered' then 1 else 0 end) as count_of_status,
       sum(case when status = 'expired' then 1 else 0 end) as count_of_expired,
       sum(case when status = 'delivery failed' then 1 else 0 end) as count_of_delivery_failed
from TABLEB b
group by b.SMS_SHORT_CODE ;

Note that no JOIN is necessary. All the data you want to aggregate is in TABLEB.