Sorry, I was using "rollup" generically as in the use of all the max and min statements. I was thinking of something like this:
SELECT Convert(varchar(10), [Groupby date derived], 120) AS [Groupby date],
[Timestamp],
[PTL-3 Max Temp],
[PTL-3 Min Temp],
[PTL-3 Avg Temp],
[PTL-2 Max Temp],
[PTL-2 Min Temp],
[PTL-3 Avg Temp],
[PCW 4 Max PSI],
[PCW 4 Min PSI],
[PCW 4 Avg PSI],
[PCW 2 Max PSI],
[PCW 2 Min PSI],
[PCW 2 Avg PSI],
[tag4 _max],
[tag4 _min],
[tag4 _Avg],
[tag5 _max],
[tag5 _min],
[tag5 _Avg],
[PTL-2 Max Hum],
[PTL-2 Min Hum],
[PTL-2 Avg Hum],
[PTL-3 Max Hum],
[PTL-3 Min Hum],
[PTL-3 Avg Hum],
[CDA Max PSI],
[CDA Min PSI],
[CDA Avg PSI],
[City Max pH],
[City Min pH],
[City Avg pH],
[CWS B Max Temp],
[CWS B Min Temp],
[CWS B Avg Temp],
[CWS A Max Temp],
[CWS A Min Temp],
[CWS A Avg Temp]
FROM (
SELECT
DATEADD(DAY, DATEDIFF(DAY, 0, HistoricalData.DateAndTime), 0) AS 'Groupby date derived',
Max(HistoricalData.DateAndTime) AS 'Timestamp',
MAX(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Max Temp',
MIN(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Min Temp',
AVG(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Avg Temp',
MAX(CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Max Temp',
MIN(CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Min Temp',
AVG(CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Avg Temp',
MAX(CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS 'PCW 4 Max PSI',
MIN(CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS 'PCW 4 Min PSI',
AVG(CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS 'PCW 4 Avg PSI',
MAX(CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val/10) END) AS 'PCW 2 Max PSI',
MIN(CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val/10) END) AS 'PCW 2 Min PSI',
AVG(CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val/10) END) AS 'PCW 2 Avg PSI',
MAX(CASE WHEN (HistoricalData.TagIndex) = '4' THEN (HistoricalData.Val/10) END) AS 'tag4 _max',
MIN(CASE WHEN (HistoricalData.TagIndex) = '4 ' THEN (HistoricalData.Val/10) END) AS 'tag4 _min',
AVG(CASE WHEN (HistoricalData.TagIndex) = '4' THEN (HistoricalData.Val/10) END) AS 'tag4 _Avg',
MAX(CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val/10) END) AS 'tag5 _max',
MIN(CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val/10) END) AS 'tag5 _min',
AVG(CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val/10) END) AS 'tag5 _Avg',
MAX(CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Max Hum',
MIN(CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Min Hum',
AVG(CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Avg Hum',
MAX(CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Max Hum',
MIN(CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Min Hum',
AVG(CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Avg Hum',
MAX(CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val/10) END) AS 'CDA Max PSI',
MIN(CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val/10) END) AS 'CDA Min PSI',
AVG(CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val/10) END) AS 'CDA Avg PSI',
MAX(CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/10) END) AS 'City Max pH',
MIN(CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/10) END) AS 'City Min pH',
AVG(CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/10) END) AS 'City Avg pH',
MAX(CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val/10) END) AS 'CWS B Max Temp',
MIN(CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val/10) END) AS 'CWS B Min Temp',
AVG(CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val/10) END) AS 'CWS B Avg Temp',
MAX(CASE WHEN (HistoricalData.TagIndex) = '11' THEN (HistoricalData.Val/10) END) AS 'CWS A Max Temp',
MIN(CASE WHEN (HistoricalData.TagIndex) = '11' THEN (HistoricalData.Val/10) END) AS 'CWS A Min Temp',
AVG(CASE WHEN (HistoricalData.TagIndex) = '11' THEN (HistoricalData.Val/10) END) AS 'CWS A Avg Temp'
FROM rsview.dbo.HistoricalData HistoricalData
WHERE (HistoricalData.DateAndTime>?) AND (HistoricalData.DateAndTime<?) AND (HistoricalData.Val <>0)
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, HistoricalData.DateAndTime), 0)
) AS derived_table
ORDER BY [Groupby date derived]