在SQL中，可能會將兩行合二為一，其中一些行元素變成列

通常，每個付款人有兩行，一行表示成功計數，另一行表示失敗計數。

我想讓兩行回傳一個成功和失敗列。

但有時只有一行，要么成功，要么失敗，但不能兩者兼而有之。

我已經嘗試自行加入源表，左右連接不會獲取丟失的成功或丟失的失敗。完全連接會為醫療保險行回傳四行，這真的很麻煩。

==> 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

uj5u.com熱心網友回復：

您可以使用 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

uj5u.com熱心網友回復：

你基本上是在一個pivot之后，你可以聚合和使用一個條件 case 運算式，未經測驗，但類似于：

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;

uj5u.com熱心網友回復：

你可以試試這個：

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

標籤：sql 加入

上一篇：Oracle-SQL加入重復列但避免重復行

下一篇：如何查找SQLite資料庫中所有列的資訊？