Home > Back-end >  In SQL potentially turn two rows into one with some of the row elements becoming columns
In SQL potentially turn two rows into one with some of the row elements becoming columns

Time:02-18

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

CodePudding user response:

You can use a self-join:

select p.corpname, p.payername, p.planname, p.payerid, max(coalesce(p1.`count`, 0)), max(coalesce(p2.`count`, 0)) from plans p 
left join plans p1 on p.payerid = p1.payerid and p1.status='SUCCESS' 
left join  plans p2 on p.payerid = p2.payerid and p2.status='FAILURE' 
group by p.corpname, p.payername, p.planname, p.payerid

CodePudding user response:

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;

CodePudding user response:

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
  • Related