SQL Server Output Issue – Why SQL Server Returns 0

The DMV sys.dm_exec_sessions has column host_name which is the computer at the client end of the connection. You can join that to your posted query through the session_id.

I think you're doing too much work.

If this is the desired command:

SELECT u.MySingle
       ,CASE u.Id WHEN 1 THEN CONVERT(VARCHAR(20), MaxScheduled, 103) ELSE '' END AS [amy]
       ,CASE u.Id WHEN 2 THEN CONVERT(VARCHAR(20), MaxScheduled, 103) ELSE '' END AS [pal]
       ,CASE u.Id WHEN 3 THEN CONVERT(VARCHAR(20), MaxScheduled, 103) ELSE '' END AS [chin]
FROM #users u
     LEFT JOIN (SELECT UserId, MAX(DateScheduled) AS MaxScheduled
                FROM #roadster
                GROUP BY UserId) MDates
     ON MDates.UserId = u.Id;

You can do something like this:

DECLARE @SQL1 VARCHAR(MAX);

SELECT @SQL1 = 'SELECT MySingle ' 
                + (SELECT ',' + 'CASE u.Id WHEN ' + CAST(u.Id AS VARCHAR(10))
                              + ' THEN CONVERT(VARCHAR(20), MaxScheduled, 103) ELSE '''' END AS ['
                              + u.MySingle + ']'
                   FROM #users u
                   FOR XML PATH(''))
                + ' FROM #users u
                   LEFT JOIN (SELECT UserId, MAX(DateScheduled) AS MaxScheduled
                   FROM #roadster
                   GROUP BY UserId) MDates
                   ON MDates.UserId = u.Id;';

EXEC (@SQL1);

+----------+------------+------------+------------+
| MySingle | amy        |     pal    |    chin    |
+----------+------------+------------+------------+
| amy      | 01/03/2017 |            |            |
+----------+------------+------------+------------+
| pal      |            | 02/03/2017 |            |
+----------+------------+------------+------------+
| chin     |            |            | 03/03/2017 |
+----------+------------+------------+------------+

Check it here: http://rextester.com/CCIXF68831