CODEWITHSUNDEEP
sqlsql-serverdatabase
Santosh
Santosh

Reputation: 23

SQL table Multiple rows for a id into multiple columns

I am trying to get data from 2 different tables and put the combined data into a report. Each claim number can have one of three possible statuses: approved, denied, appealed. An appeal can be either successful or denied.

Currently, each claim ID has multiple entries, but I want each claim grouped on a single record with each of the possible statuses as fields. Any ideas on best approach to get the output as below?

Table 1

Claim       AddDate     AddUser    
1234        08/01/2017  Catan    
4567        08/02/2017  Jigsu    
7890        08/07/2017  Panama

Table 2

Claim      Notes             NoteType
1234       Denied            2
1234       Appeal Success    3
4567       Approved          1
7890       Denied            2
7890       Appeal Denied     3

OUTPUT TABLE

Claim     ApprovalNote        DenialNote      AppealNote
1234      NULL                Denied          Appeal Success    
4567      Approved            NULL            NULL    
7890      NULL                Denied          Appeal Denied

I am using SQL Server 2008.

Upvotes: 1

Views: 934

Answers (5)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

SELECT uc.claim,
       uc.adduser,
       MAX(CASE WHEN nc.notetype = 1 THEN nc.notes END) AS approvalnote,
       MAX(CASE WHEN nc.notetype = 2 THEN nc.notes END) AS denialnote,
       MAX(CASE WHEN nc.notetype = 3 THEN nc.notes END) AS appealnote
  FROM user_claim uc
 INNER JOIN notes_claim nc
    ON uc.claim = nc.claim
 GROUP BY uc.claim,
          uc.adduser

Result

claim   adduser approvalnote    denialnote  appealnote
1234    Catan   NULL            Denied      Appeal Success
4567    Jigsu   Approved        NULL        NULL
7890    Panama  NULL            Denied      Appeal Denied

Upvotes: 0

Victor Hugo Terceros
Victor Hugo Terceros

Reputation: 3169

--Sample Data
declare @Table2 table(Claim int ,Notes varchar(500))
insert into @Table2
select 1234,'Denied' union
select 1234,'Appeal Success' union
select 4567,'Approved' union
select 7890,'Denied' union
select 7890,'Appeal Denied' 
--Main Query
SELECT Claim, 
[Approved] AS ApprovedNote,[Denied] AS DeniedNote, COALESCE([Appeal 
Success],[Appeal Denied]) AS AppealNote
FROM
(SELECT Claim,Notes FROM @Table2) AS SourceTable
PIVOT
(
MAX(Notes)
FOR Notes IN ([Denied],[Appeal Success],[Approved],[Appeal Denied])
) AS PivotTable;

Upvotes: 0

AJSOTTO
AJSOTTO

Reputation: 96

You can join to each sub section of Table 2 to achieve this fairly painlessly without aggregation or case statements. 

SELECT  
    t1.Claim ,
    ApprovalNote.Notes AS ApprovalNote ,
    DenialNote.Notes AS DenialNote ,
    AppealNote.Notes AS AppealNote
FROM    
    Table1 t1
    LEFT OUTER JOIN Table2 ApprovalNote 
        ON t1.Claim = ApprovalNote.Claim
        AND ApprovalNote.NoteType = 1
    LEFT OUTER JOIN Table2 DenialNote 
        ON t1.Claim = DenialNote.Claim
        AND DenialNote.NoteType = 2
    LEFT OUTER JOIN Table2 AppealNote 
        ON t1.Claim = AppealNote.Claim
        AND AppealNote.NoteType = 3;

Upvotes: 2

Tlivanios
Tlivanios

Reputation: 31

If I understand correctly, you can try

Select claim ,
Case When exists (Select 1 from table2 where table1.claim = table2.claim 
                                         and notetype = 1
     Then 'Approved' Else Null End ,

Case When exists (Select 1 from table2 where table1.claim = table2.claim 
                                         and notetype = 2
     Then 'Denial' Else Null End ,


Case When exists (Select 1 from table2 where table1.claim = table2.claim 
                                         and notetype = 3 
     Then notes Else Null End 

from table1

Upvotes: 0

Parfait
Parfait

Reputation: 107577

Consider conditional aggregation wrapped in an aggregate query:

SELECT m.Claim, Max(m.subApprovalNote) As ApprovalNote, 
                Max(m.subDenialNote) As DenialNote, 
                Max(m.subAppealNote) As AppealNote
FROM
   (SELECT t1.Claim, CASE WHEN t2.NoteType = 1
                          THEN t2.Notes
                          ELSE NULL
                     END AS subApprovalNote,
           CASE WHEN t2.NoteType = 2
                THEN t2.Notes
                ELSE NULL
           END AS subDenialNote,
           CASE WHEN t2.NoteType = 3
                THEN t2.Notes
                ELSE NULL
           END AS subAppealNote
    FROM table1 t1
    INNER JOIN table2 t2 ON t1.Claim = t2.Claim
   ) As m
GROUP BY m.Claim

Upvotes: 0

Related Questions