How to include concatenated values in a dynamic pivot column value?

Question

I am extending a dynamic pivot query. The original query works fine. In the second query, instead of displaying the date as a value for the pivot column, I would like to display a concatenated column. This concatenated column will contain datetime ~ user name ~ record **id. I can't seem to make the extended query work with a concatenated column in the pivot column value, any ideas?

Original Query**

DECLARE @PivotColumnHeaders NVARCHAR(MAX)

SELECT @PivotColumnHeaders =  
   COALESCE(
     @PivotColumnHeaders + ',[' +  cast(SystemFullName as Nvarchar) + ']',
     '[' + cast(SystemFullName as varchar)+ ']'
   )
FROM System
WHERE (@SelectedSystemIDs IS NULL OR  System.ID IN(select  * from dbo.SplitInts_RBAR_1(@SelectedSystemIDs, ',')))     
AND ((@PlatformID =0) OR  (System.PlatformID = @PlatformID) OR (@PlatformID = 12 AND System.PlatformID <= 2))  

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
   SELECT *
   FROM (
     SELECT
       TSBNumber [TSBNumber],
       SystemFullName,
       ClosedDate
     FROM ServiceEntry 
     inner JOIN System 
       ON ServiceEntry.SystemID = System.ID
     where
      (ServiceEntry.TSBNumber IS NOT NULL)
       AND 
       (ServiceEntry.ClosedDate IS NOT NULL)
       AND
       ( 
       (''' + @SelectedTsbIDs + ''' = '''+ '0' + ''') OR
         (ServiceEntry.TSBNumber in (select * from dbo.SplitStrings_Moden(''' + @SelectedTsbIDs + ''', ''' + ',' + ''')))
        )  
        AND (
         (''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = '''+ '0' + ''') 
        OR(System.PlatformID = ''' + cast(@PlatformID as varchar(10)) + ''')
        OR(''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = ''' + '12' + ''' AND System.PlatformID <=  ''' + '2' + ''')
        )
        AND
        (ServiceEntry.ClosedDate between ''' + convert(varchar(10), @StartDate, 120)  + '''  and   ''' +  convert(varchar(10), @EndDate, 120) + ''')      
  )

   ) AS PivotData
   PIVOT (
     MAX(ClosedDate)
     FOR SystemFullName IN (
       ' + @PivotColumnHeaders + '
     ) 
   ) AS PivotTable   
' 

EXECUTE (@PivotTableSQL)

Extended Query with Concatenated Column

SELECT @PivotColumnHeaders =  
   COALESCE(
     @PivotColumnHeaders + ',[' +  cast(SystemFullName as Nvarchar) + ']',
     '[' + cast(SystemFullName as varchar)+ ']'
   )
FROM System
WHERE (@SelectedSystemIDs IS NULL OR  System.ID IN(select  * from dbo.SplitInts_RBAR_1(@SelectedSystemIDs, ',')))     
AND ((@PlatformID =0) OR  (System.PlatformID = @PlatformID) OR (@PlatformID = 12 AND System.PlatformID <= 2))  

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
   SELECT *
   FROM (
     SELECT
       TSBNumber [TSBNumber],
       SystemFullName,
       CONVERT(VARCHAR(10),ServiceEntry.ClosedDate,105) + '~' + us.FirstName + us.LastName + '~' +  CAST(ServiceEntry.ID AS VARCHAR(10)) AS CONCATCOLUMN

     FROM ServiceEntry 
     inner JOIN System 
       ON ServiceEntry.SystemID = System.ID
     LEFT JOIN [dbo].[User] as US ON ServiceEntry.CreatedBy =[dbo].[User].ID as US
     where
      (ServiceEntry.TSBNumber IS NOT NULL)
       AND 
       (ServiceEntry.ClosedDate IS NOT NULL)
       AND
       ( 
       (''' + @SelectedTsbIDs + ''' = '''+ '0' + ''') OR
         (ServiceEntry.TSBNumber in (select * from dbo.SplitStrings_Moden(''' + @SelectedTsbIDs + ''', ''' + ',' + ''')))
        )  
        AND (
         (''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = '''+ '0' + ''') 
        OR(System.PlatformID = ''' + cast(@PlatformID as varchar(10)) + ''')
        OR(''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = ''' + '12' + ''' AND System.PlatformID <=  ''' + '2' + ''')
        )
        AND
        (ServiceEntry.ClosedDate between ''' + convert(varchar(10), @StartDate, 120)  + '''  and   ''' +  convert(varchar(10), @EndDate, 120) + ''')    
   ) AS PivotData
   PIVOT (
    MAX(CONCATCOLUMN)
     FOR SystemFullName IN (
       ' + @PivotColumnHeaders + '
     ) 
   ) AS PivotTable   
' 

Answer 1

You can use REPLACE on the date column in the @PivotColumnHeaders string to build the concatenated column.

SELECT @PivotColumnHeaders =  
   COALESCE(
     @PivotColumnHeaders + ',[' +  cast(SystemFullName as Nvarchar) + ']',
     '[' + cast(SystemFullName as varchar)+ ']'
   )
FROM System
WHERE (@SelectedSystemIDs IS NULL OR  System.ID IN(select  * from dbo.SplitInts_RBAR_1(@SelectedSystemIDs, ',')))     
AND ((@PlatformID =0) OR  (System.PlatformID = @PlatformID) OR (@PlatformID = 12 AND System.PlatformID <= 2))  

REPLACE(@PivotColumnHeaders, *datecolumn*, *datecolumn* + '' '' + username + '' '' + CONVERT(nvarchar(10), recorded))