Btukfyl

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.

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 > '2018-11-17 00:00:00.00' AND transactionType = 'BulkImport' AND profileId = 122 

  order by sitecode, completed desc

Any help thanks.
Even if Pivot would not work, can someone point me to a better logic

You can convert those sub-queries to OUTER APPLY

SELECT 

    Parent.id

    ,[sitecode]

    ,[started]

    ,[completed]

    ,cast(( completed - started) as time)               

    ,[profileId]

    ,Child.Dem

    ,Child.Clincial        

    ,Child.ValidPatientTypes

    ,Child.NotValidPatientTypes

FROM    [Backend].[dbo].[Transaction] AS Parent

    OUTER APPLY

    (

        SELECT  TOP 1 

            MAX (CASE WHEN Child.message like '%Initialized%' AND Child.message like '%demographic records%'

                  THEN CAST(RTRIM(LTRIM(REPLACE(REPLACE(CAST(Child.message as NVarchar(4000)) , 'Initialized', ''),'demographic records',''))) AS NVarchar(4000))

                  END) AS Dem,

            MAX (CASE WHEN Child.message like '%Initialized%' AND child.message like '%clinical records%'

                  THEN CAST(RTRIM(LTRIM(REPLACE(REPLACE(CAST(Child.message as NVarchar(4000)) , 'Initialized', ''),'clinical records',''))) AS NVarchar(4000))

                  END) AS Clinical,

            MAX (CASE WHEN Child.message like 'Total valid patient types:%'

                  THEN CAST(RTRIM(LTRIM(REPLACE(CAST(Child.message as NVarchar(4000)),'Total valid patient types:',''))) AS NVarchar(4000)) 

                  END) AS ValidPatientTypes,

            MAX (CASE WHEN Child.message like 'Total invalid patient types:%'

                  THEN CAST(RTRIM(LTRIM(REPLACE(CAST(Child.message as NVarchar(4000)),'Total invalid patient types:',''))) AS NVarchar(4000))

                  END) AS NotValidPatientTypes

        FROM    [backend].[dbo].[Transaction_StatusMessage] AS Child 

        WHERE   Child.transactionId = Parent.Id  

        ORDER BY Child.created DESC

    ) Child

WHERE completed       > '2018-11-17 00:00:00.00' 

AND   transactionType = 'BulkImport' 

AND   profileId       = 122 

ORDER BY sitecode, completed desc