title: for same Id duplicate record with null values have to combine into one row using cte. how can we achieve in SQL server.my code is
body: <pre><code>with cte as (
  
  select distinct InvoiceId
              ,case when [Action] like '%Generated%'   then ActionOn   end as InvoiceGeneratedon
              ,case when [Action] like '%PM%' then ActionOn end as pmacceptedon
              ,case when [Action] like '%Reworked%' then ActionOn end as FNAaccepted
              ,case when [Action] like '%Invoice Submitted to Customer%' then ActionOn end as submitCustomer
  
      from [INF_TransactionHistory]  
   ) 
      into #temp1 from cte 
  
      select distinct InvoiceId ,InvoiceGeneratedon,pmacceptedon,FNAaccepted ,submitCustomer
                ,DATEDIFF(day , pmacceptedon , InvoiceGeneratedon)  as DaysTakenByPM
               ,DATEDIFF(day , submitCustomer , pmacceptedon)  as DaysTakenByFNA 
               ,DATEDIFF(day , submitCustomer , InvoiceGeneratedon)  as TotalDays   from #temp1
       where InvoiceId=5401600343  
       group by InvoiceId
              ,InvoiceGeneratedon,pmacceptedon,FNAaccepted ,submitCustomer
  
  drop table #temp1
  </code></pre>