title: Removing Sub queries to PIVOT table
body: <p>I have a query that uses sub queries, but I would like to make it more efficient and easier to work.  I've been looking at PIVOT logic, but can't seem to be able to get it to work.  Below is the original sub query logic, can some one help me or point me in the correct direction.</p>
  
  <pre><code>SELECT 
      Parent.id
      ,[sitecode]
      ,[started]
      ,[completed]
      ,cast(( completed - started) as time)               
      ,[profileId]
      ,(SELECT TOP 1 CAST(RTRIM(LTRIM(REPLACE(REPLACE(CAST(Child.message as NVarchar(4000)) , 'Initialized', ''),'demographic records',''))) AS NVarchar(4000)) FROM [backend].[dbo].[Transaction_StatusMessage] AS Child WHERE Child.transactionId =Parent.Id  AND child.message like '%Initialized%' AND child.message like '%demographic records%'  ORDER BY Child.created DESC) AS Dem
      ,(SELECT TOP 1 CAST(RTRIM(LTRIM(REPLACE(REPLACE(CAST(Child.message as NVarchar(4000)) , 'Initialized', ''),'clinical records',''))) AS NVarchar(4000)) FROM [backend].[dbo].[Transaction_StatusMessage] AS Child WHERE child.message like '%Initialized%' AND child.message like '%clinical records%' AND Child.transactionId =Parent.Id ORDER BY Child.created DESC) AS Clincial        
      ,(SELECT TOP 1 CAST(RTRIM(LTRIM(REPLACE(CAST(Child.message as NVarchar(4000)),'Total valid patient types:',''))) AS NVarchar(4000)) FROM [backend].[dbo].[Transaction_StatusMessage] AS Child WHERE child.message like 'Total valid patient types:%' AND Child.transactionId =Parent.Id ORDER BY Child.created DESC) AS ValidPatientTypes
      ,(SELECT TOP 1 CAST(RTRIM(LTRIM(REPLACE(CAST(Child.message as NVarchar(4000)),'Total invalid patient types:',''))) AS NVarchar(4000)) FROM [backend].[dbo].[Transaction_StatusMessage] AS Child WHERE child.message like 'Total invalid patient types:%' AND Child.transactionId =Parent.Id ORDER BY Child.created DESC) AS NotValidPatientTypes
    FROM [Backend].[dbo].[Transaction] AS Parent
    where completed &gt; '2018-11-17 00:00:00.00' AND transactionType = 'BulkImport' AND profileId = 122 
    order by sitecode, completed desc
  </code></pre>
  
  <p>Any help thanks.
  Even if Pivot would not work, can someone point me to a better logic </p>