Query with CASE expressions on a Logger Devices database
You can get it by using PIVOT relational operator, but it requires a dynamic query due you don't know the name of SiteName in advance.

The STUFF query builds a coma-separated string with the names of the sites. The PIVOT query use its to get MAX(Depth) of each site.

DECLARE @col AS nvarchar(max);
DECLARE @cmd AS nvarchar(max);

SET @cols = STUFF((SELECT ',' + QUOTENAME(sName) 
                   FROM loggerData2
                   WHERE sName = sName IN ('SiteName1', 'SiteName2', ...)
                   FOR XML PATH(''), TYPE
                   ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @cmd =    'SELECT DT, ' + @col
            + 'FROM 
               (
                   SELECT 
                      sName as SiteName,
                      CAST(readingDTTM AS DATETIME) DT,
                      lValue as Depth
                   FROM 
                     loggerData2
                   WHERE 
                     sName = sName IN ('SiteName1', 'SiteName2', ...)
                     AND Sensor = 'Depth'
               ) src
               PIVOT
               (
                    MAX(Depth) FOR SiteName IN (' + @col + ')'
            + ') pvt';

EXEC sp_executesql @cmd;