In SQL potentially turn two rows into one with some of the row elements becoming columns

Question

Generally, there are two rows for each payer, with one row representing a success count and another row representing a failure count.

I want to have the two rows return as one with both a success and a failure column.

But sometimes there is only one row, either a success or a failure but not both.

I've tried joining the source table on itself, a left and right join don't pick up either the missing success or the missing failure. A full join returns four rows for the medicare row which really scrogges things up.

==> source data below <==

CorpName           PayerName                 PlanName                 PayerID Status  PlanUid  Count
------------------ ------------------------- ------------------------ ------- ------- -------- -----
Medicare of Texas  Novitas Solution          Medicare - Texas, Part B 04412   FAILURE 660FED8E  19
Medicare of Texas  Novitas Solution          Medicare - Texas, Part B 04412   SUCCESS 660FED8E  29
GHI PPO            GHI PPO                   Group Health Inc. - New  13551   FAILURE BFF5E581   1
United Healthcare  Benefits of Texas, Inc.   United Healthcare        87726   SUCCESS 9C1E2A67   5


==> desired output <==

CorpName           PayerName                 PlanName                 PayerID PlanUid  Success Failure
------------------ ------------------------- ------------------------ ------- -------- ------- -------
Medicare of Texas  Novitas Solution          Medicare - Texas, Part B 04412   660FED8E  29       19
GHI PPO            GHI PPO                   Group Health Inc. - New  13551   BFF5E581   0        1
United Healthcare  Benefits of Texas, Inc.   United Healthcare        87726   9C1E2A67   5        0

Answer 1

You are basically after a pivot, you can aggregate and use a condition case expression, untested but something like:

select
    max(CorpName)  CorpName, 
    max(PayerName) PayerName, 
    max(PlanName)  PlanName, 
    max(PayerID)   PayerID, 
    max(PlanUid)   PlanUid, 
    coalesce(case when status='SUCCESS' then "count" end,0) as Success,
    coalesce(case when status='FAILURE' then "count" end,0) as Failure
group by 
    CorpName, PayerName, PlanName, PayerID, PlanUid;

Answer 2

You can try this:

IF (OBJECT_ID('tempdb..#MyTable') IS NOT NULL)
    BEGIN
      DROP TABLE #MyTable
    END;
  IF (OBJECT_ID('tempdb..#product') IS NOT NULL)
    BEGIN
      DROP TABLE #product
    END;
    
CREATE TABLE #MyTable (CorpName varchar(50) NOT NULL, PayerName varchar(50) NOT NULL, PlanName varchar(50)  NOT NULL, PayerID varchar(20)  NOT NULL, [Status] varchar(20) NOT NULL, PlanUid varchar(20), [COUNT] int not null)

INSERT INTO #MyTable (CorpName ,PayerName,PlanName ,PayerID, [Status] , PlanUid, [Count]) 
    Values ('Medicare of Texas','Novitas Solution', 'Medicare - Texas, Part B','04412','FAILURE', '660FED8E',  19),
     ('Medicare of Texas','Novitas Solution', 'Medicare - Texas, Part B','04412','SUCCESS', '660FED8E',  29),
     ('GHI PPO','GHI PPO', 'Group Health Inc. - New' , '13551',   'FAILURE', 'BFF5E581',  1),
     ('United Healthcare',  'Benefits of Texas, Inc.',   'United Healthcare',  '87726','SUCCESS','9C1E2A67', 5)

-- select * from #MyTable
select CorpName ,PayerName,PlanName ,PayerID,PlanUid
, sum(coalesce(case when [status] = 'SUCCESS' then ([count]) end, 0)) as SuccessCnt
, sum(coalesce(case when [status] = 'FAILURE' then ([count]) end, 0)) as FailureCnt
from #MyTable
group by CorpName ,PayerName,PlanName ,PayerID,PlanUid