0
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.

  • 1
    Please add loggerData2 table schema. What columns define the sitename? – McNets Jan 10 at 11:10
  • sNAME = site name - thank you – stephen smith Jan 10 at 11:12
  • I am running this in vba - sorry about that. – stephen smith Jan 10 at 11:15
  • I have changed sql query sorry about that – stephen smith Jan 10 at 11:16
  • Is there a limited and well-known number of sitenames? – McNets Jan 10 at 11:18
3

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;

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.