Using "CASE in Select statement

Transact-SQL
1

I am accessing a SQL server through Excel. I have a column that contains data named "Val".there are 11 different inputs that are identified by a "TagIndex" number 0-10. I want a n output column for each of the TagIndex numbers.

I am getting an Incorrect Syntax near ''' fault when I run it. It's difficult to know exactly what the error is. I have been over this thing and over it and over it. Does anybody see anything obviously wrong here?

SELECT HistoricalData.DateAndTime
,(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Temp Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Temp Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS 'PCW 4 Temp Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val/10) END) AS 'PCW 4 PSI Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '4' THEN (HistoricalData.Val/10) END) AS ‘PCW 2 PSI Raw’
, (CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val/10) END) AS ‘PCW 2 Temp Raw’
, (CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Hum Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Hum Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val/10) END) AS 'CDA PSI Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/10) END) AS 'City pH Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '11' THEN (HistoricalData.Val/10) END) AS 'CWS A Temp Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val/10) END) AS 'CWS B Temp Raw'

FROM rsview.dbo.HistoricalData HistoricalData
WHERE (HistoricalData.DateAndTime>?) AND (HistoricalData.DateAndTime<?) AND (HistoricalData.Val <>0)

Thanks for your consideration
Glen

2

SQL Server does not recognize backticks as quote terminators. You have some of those in the code you posted (for PCW 2 PSI Raw, PCW 2 Temp Raw), . I have fixed that in the code below. Apart from that, I am not able to comment on whether the logic is correct or not.

SELECT HistoricalData.DateAndTime
,(CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Temp Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Temp Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '2' THEN (HistoricalData.Val/10) END) AS 'PCW 4 Temp Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '3' THEN (HistoricalData.Val/10) END) AS 'PCW 4 PSI Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '4' THEN (HistoricalData.Val/10) END) AS 'PCW 2 PSI Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '5' THEN (HistoricalData.Val/10) END) AS 'PCW 2 Temp Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '6' THEN (HistoricalData.Val/10) END) AS 'PTL-2 Hum Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '7' THEN (HistoricalData.Val/10) END) AS 'PTL-3 Hum Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '8' THEN (HistoricalData.Val/10) END) AS 'CDA PSI Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '9' THEN (HistoricalData.Val/10) END) AS 'City pH Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '11' THEN (HistoricalData.Val/10) END) AS 'CWS A Temp Raw'
, (CASE WHEN (HistoricalData.TagIndex) = '10' THEN (HistoricalData.Val/10) END) AS 'CWS B Temp Raw'

FROM rsview.dbo.HistoricalData HistoricalData
WHERE (HistoricalData.DateAndTime>?) AND (HistoricalData.DateAndTime<?) AND (HistoricalData.Val <>0)
1 Like
3

Thank you! That was the issue. Now I have a different issue that will probably require another post. The query ran, but each TagIndex data point has a unique "DateAndTime". So for each data point I have 10 blank records for that "DateAndTime". Makes a real ugly table. It looks like I need to run a separate query for each TagIndex number.

Could this still work if I ran the CASE statements in the WHERE?

4

are you forming a pivot table ?

Since you are extracting the data to excel, you can let Excel do it for you.

Alternatively to do it in SQL side, you can use PIVOT or something like what you have done here, but with slight modification.

SELECT HistoricalData.DateAndTime
, MAX((CASE WHEN (HistoricalData.TagIndex) = '0' THEN (HistoricalData.Val/10) END)) AS 'PTL-3 Temp Raw'
, MAX((CASE WHEN (HistoricalData.TagIndex) = '1' THEN (HistoricalData.Val/10) END)) AS 'PTL-2 Temp Raw'
.....
FROM rsview.dbo.HistoricalData HistoricalData
WHERE (HistoricalData.DateAndTime>?) AND (HistoricalData.DateAndTime<?) AND (HistoricalData.Val <>0)
GROUP BY HistoricalData.DateAndTime

Since you mention the "DateAndTime" is unique, the result might still be "ugly". Depending on how you want the result, this should give you something to start with to tune the query