The problem is because
'500'
or
500
(etc) does not match the rules for
Database Identifiers[
^]
So you need to delimit the list with square brackets e.g.
FOR [CONCENTRATION] IN ([500],[300],[100],[30],[10],[3],[1],[0.3],[0.1],[0.03],[0.01])
[Edit]. Note that you do not need to use
['500'], ['300']
etc as these are
column names not values.
You are also going to get an error with
)AS pivot
pivot is as reserved word, use
)AS p
[EDIT] To answer the next part of your problem.
Your query is currently
SELECT *
FROM
(
SELECT TOP 1000 [WELLPOSITION]
,[COMPOUND_NAME]
,[CON_VALUE]
,[VALUE]
,[DATA_TYPE]
,[PLATE_ID]
FROM [AFileStorageDB].[dbo].[View_1] where DATA_TYPE='% Inhibition'
)as s
PIVOT
(
SUM(VALUE)
FOR CON_VALUE IN ([500],[300],[100],[30],[10],[3],[1],[0.3],[0.1],[0.03],[0.01])
)AS p
ORDER BY [COMPOUND_NAME]
The reason you are getting multiple lines of COMPOUND_NAME 7977797 (for example) is because you have included
[WELLPOSITION]
(for whatever reason you did not show that column in your actual results).
If you remove that from the query you will get the results you need i.e.
SELECT *
FROM
(
SELECT [COMPOUND_NAME]
,[CON_VALUE]
,[VALUE]
,[DATA_TYPE]
,[PLATE_ID]
FROM [View_1]
)as s
PIVOT
(
SUM(VALUE)
FOR CON_VALUE IN ([500],[300],[100],[30],[10],[3],[1],[0.3],[0.1],[0.03],[0.01])
)AS p
ORDER BY [COMPOUND_NAME]
will give you a single row for each COMPOUND_NAME
7977797
STOCK-1S-21278
STOCK-1S-21279
STOCK-1S-21280
STOCK-1S-21287
STOCK-1S-21288
Z969076296
There will still be several NULL values in your results which you could deal with by replacing the
SELECT *
with
SELECT COMPOUND_NAME, [DATA_TYPE], [PLATE_ID],
ISNULL([500],0),ISNULL([300],0),ISNULL([100],0),ISNULL([30],0),
ISNULL([10],0),ISNULL([3],0),ISNULL([1],0),ISNULL([0.3],0),
ISNULL([0.1],0),ISNULL([0.03],0),ISNULL([0.01],0)