Query with CASE expressions on a Logger Devices database select t.DATETIME,  max(t.Depth) Depth,  max(t.Temperature) Temperature from(
                SELECT CAST(readingDTTM AS DATETIME) DATETIME, 
                case Sensor when 'Depth' then lValue end as Depth, 
                case Sensor when 'Temperature' then lValue end as Temperature
                FROM loggerData2 WHERE sName = 'Easington' AND 
                Sensor in ('Depth','Temperature')
                GROUP BY CAST(readingDTTM AS DATETIME), lValue, Sensor
   )t 
group by t.DATETIME ORDER BY t.DATETIME DESC; 


The above query shows the following results when querying my database table called loggerData2.
It gets a distinct DATETIME based on both Depth and Temperature (Sensor in the database table).

DATETIME              Depth     Temperature
-------------------   ------    -----------     
09/10/2018 10:30:00   48.670    10.35   
09/10/2018 10:00:00   48.670    10.35   
09/10/2018 09:30:00   48.670    10.35   
09/10/2018 09:00:00   48.670    10.35   
09/10/2018 08:30:00   48.670    10.35   
09/10/2018 08:00:00   48.670    10.35   
09/10/2018 07:30:00   48.680    10.35   
09/10/2018 07:00:00   48.670    10.35   
09/10/2018 06:30:00   48.670    10.35   


My problem now is this is only set up for one site at a time.

What I am trying to do is the same as the above query, but this time for only Depth (sensor) but for multiple sites (sName).

DATE TIME             Depth (sitename1)   Depth (sitename2)
-------------------   -----------------   -----------------
09/10/2018 10:30:00   48.670              46.756    
09/10/2018 10:00:00   48.670              46.756    
09/10/2018 09:30:00   48.670              46.756    
09/10/2018 09:00:00   48.670              46.756    
09/10/2018 08:30:00   48.670              46.756    
09/10/2018 08:00:00   48.670              46.756    
09/10/2018 07:30:00   48.680              46.756    
09/10/2018 07:00:00   48.670              46.756    
09/10/2018 06:30:00   48.670              46.756    


Any ideas would be greatly appreciated.
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;