请纠正这个问题 [英] please correct the querry

问题描述

请更正querry

please correct the querry

/****** Script for SelectTopNRows command from SSMS  ******/


 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]

感谢您的回复它正在运作..结果如下









COMPOUND_NAME DATA_TYPE PLATE_ID 5 00 300 100 30

7977797％抑制板2 19.67334 NULL NULL NULL

7977797％抑制板2 NULL NULL NULL NULL

7977797％抑制板2 NULL NULL NULL NULL

7977797％抑制板2 NULL -3.99334 NULL NULL

7977797％抑制板2 NULL NULL -7.92299 NULL

7977797 ％抑制板2 NULL NULL NULL -27.33510

7977797％抑制板2 NULL NULL NULL NULL

7977797％抑制板2 NULL NULL NULL NULL

7977797％抑制板2 NULL NULL NULL NULL

7977797％抑制板2 NULL NULL NULL NULL

7977797％抑制板2 NULL NULL NULL NULL

7977797％抑制板2 17.68691 NULL NULL NULL

7977797％抑制板2 NULL NULL NULL NULL

7977797％抑制板2 NULL NULL NULL NULL

7977797％抑制板2 NULL -7.68289 NULL NULL

7977797％抑制板2 NULL NULL -7.70530 NU LL

7977797％抑制板2 NULL NULL NULL -33.07991

相同的复合id包含500,300,100,10等的值，但它显示在另一行而不是下一列

喜欢

7977797 500value 300 value，100value等



i想删除那些空值并使其成为现实逐列...

完成500到0.01后的下一行它必须到下一行并再次填充值

thank s for your response it is working.. and result as follows




COMPOUND_NAME DATA_TYPE PLATE_ID 500 300 100 30
7977797 % Inhibition Plate 2 19.67334 NULL NULL NULL
7977797 % Inhibition Plate 2 NULL NULL NULL NULL
7977797 % Inhibition Plate 2 NULL NULL NULL NULL
7977797 % Inhibition Plate 2 NULL -3.99334 NULL NULL
7977797 % Inhibition Plate 2 NULL NULL -7.92299 NULL
7977797 % Inhibition Plate 2 NULL NULL NULL -27.33510
7977797 % Inhibition Plate 2 NULL NULL NULL NULL
7977797 % Inhibition Plate 2 NULL NULL NULL NULL
7977797 % Inhibition Plate 2 NULL NULL NULL NULL
7977797 % Inhibition Plate 2 NULL NULL NULL NULL
7977797 % Inhibition Plate 2 NULL NULL NULL NULL
7977797 % Inhibition Plate 2 17.68691 NULL NULL NULL
7977797 % Inhibition Plate 2 NULL NULL NULL NULL
7977797 % Inhibition Plate 2 NULL NULL NULL NULL
7977797 % Inhibition Plate 2 NULL -7.68289 NULL NULL
7977797 % Inhibition Plate 2 NULL NULL -7.70530 NULL
7977797 % Inhibition Plate 2 NULL NULL NULL -33.07991
same compound id contains the values for 500,300,100,10 and so on but it displays in another row instead of next column
like
7977797 500value 300 value ,100value and so

i want to remove those null values and make it column by column...
after completing 500 to 0.01 values next it must come next row and again fill the values

推荐答案

问题是因为'500'或 500 （等）与数据库标识符 [ ^ ]



所以你需要用方括号分隔列表，例如

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])

。请注意，您不需要使用 ['500']，['300'] 等，因为这些是列名而不是值。 />


你也会得到

. 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作为保留字，使用

pivot is as reserved word, use

)AS p

回答你问题的下一部分。

你的查询目前

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]

您获得多行COMPOUND_NAME 7977797的原因（例如）是因为你已经包含了 [WELLPOSITION] （无论出于什么原因你没有展示实际结果中的那一列）。



如果你从查询中删除它，你将得到你需要的结果，即

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]

将为每个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   

结果中仍会有几个NULL值，您可以处理将 SELECT * 替换为

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)

 SELECT *
FROM
 (
     SELECT  [COMPOUND_NAME]
      ,[CONCENTRATION]

       FROM [AFileStorageDB].[dbo].[CMP_FILE_REG]
       )as s
PIVOT
(
SUM([WELL_POSITION])
FOR [CONCENTRATION] IN ('500','300','100','30','10','3','1','0.3','0.1','0.03','0.01')
)AS pivot

这篇关于请纠正这个问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！