How to query the same column twice based on conditions of another column?

Question

I am a beginner with TSQL and was hoping to get some guidance on conditions with TSQL

Is there a way for the same SQL field to queried as different columns? My field name changes as the file moves from different statuses we have defined and I am attempting to use the associated timestamp from the status change to analyze some trends . For example the associated data for a file would look something like this:

<File_History
Loanstatusdescriptor= “Underwrite Assigned” Status.EventTime= “4/1/2017 12:05”>

<File_History
        Loanstatusdescriptor= “Closing Request Received” Status.EventTime= “4/3/2017 17:15”>

I have a report I am creating that is based on the statuses of certain files as they move through the workflow process. Would the query look something like:

SELECT

LoanInfo.UnderwriterName

,File_History.Status.EventTime (IF loanstatusdescriptor= “Underwrite Assigned”) AS ‘Underwriter Assigned Date’

,File_History.Status.EventTime ( IF loanstatusdescriptor= “Closing Request Received”) AS ‘Closing Request Receieved Date’

….

The End Product Would Look something like

    Underwriter|Underwriter Assigned Date |Closing Request Received Date  
    Bobby Brown    4/1/2017                 4/3/2017
    Sally Jones    4/7/2017                 4/9/2017
    Chris Graff    4/6/2017                 4/17/2017

How would I write out the T-SQL Statements that would allow the columns to be created based on loan status?

Answer 1

Assuming File_History_Status is a table that can be joined to LoanInfo:

Using conditional aggregation with a known amount of columns:

select 
    li.UnderwriterName
  , [Underwriter_Assigned_Date] = max(case 
      when fhs.LoanStatusDescriptor = 'Underwriter Assigned' 
        then fhs.EventTime 
      end)
  , [Closing_Request_Received_Date] = max(case 
      when fhs.LoanStatusDescriptor = 'Closing Request Received' 
        then fhs.EventTime 
      end)
from LoanInfo li
  inner join File_History_Status fhs
    on li.LoanId = fhs.LoanId
group by li.UnderwriterName