please correct the querry

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

SQL

/****** 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]

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

Posted 25-Sep-15 0:59am

Member 11819086

Updated 25-Sep-15 3:20am

v2

Add a Solution

Comments

Member 12003400 25-Sep-15 7:11am

what is datatype for CONCENTRATION field?

Member 11819086 25-Sep-15 7:14am

varchar or nvarchar

CHill60 25-Sep-15 9:27am

Can you provide some example data from SELECT * FROM [AFileStorageDB].[dbo].[View_1] where DATA_TYPE='% Inhibition'

Member 11819086 25-Sep-15 9:32am

WELLPOSITION COMPOUND_NAME CON_VALUE VALUE DATA_TYPE REPLICATES CONCENTRATION ASSAY_TYPE PLATE_ID Expr1
A1 7977797 500 19.67334 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
A10 7977797 0.03 -93.38832 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
A11 7977797 0.01 -184.94661 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
A2 7977797 300 -3.99334 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
A3 7977797 100 -7.92299 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
A4 7977797 30 -27.33510 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
A5 7977797 10 -145.27402 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
A6 7977797 3 -86.26695 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
A7 7977797 1 -39.03680 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
A8 7977797 0.3 102.36552 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
A9 7977797 0.1 -39.20167 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
B1 7977797 500 17.68691 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
B10 7977797 0.03 -65.32619 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
B11 7977797 0.01 104.08864 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
B2 7977797 300 -7.68289 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
B3 7977797 100 -7.70530 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
B4 7977797 30 -33.07991 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
B5 7977797 10 -40.45419 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
B6 7977797 3 -58.82987 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
B7 7977797 1 -47.70282 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
B8 7977797 0.3 -51.27712 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
B9 7977797 0.1 -50.22628 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
C1 Z969076296 500 23.80387 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
C10 Z969076296 0.03 -80.12518 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
C11 Z969076296 0.01 103.20667 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
C2 Z969076296 300 -4.32068 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
C3 Z969076296 100 -0.92566 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
C4 Z969076296 30 -17.48859 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
C5 Z969076296 10 -51.90458 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
C6 Z969076296 3 -108.45382 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
C7 Z969076296 1 -46.60636 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
C8 Z969076296 0.3 -84.65667 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
C9 Z969076296 0.1 -89.29941 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
D1 Z969076296 500 11.44110 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
D10 Z969076296 0.03 -104.77148 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
D11 Z969076296 0.01 -84.13325 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
D2 Z969076296 300 -5.70366 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
D3 Z969076296 100 -18.11205 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
D4 Z969076296 30 -112.88768 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
D5 Z969076296 10 -64.70993 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
D6 Z969076296 3 -117.24950 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
D7 Z969076296 1 -39.55541 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
D8 Z969076296 0.3 -59.28526 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
D9 Z969076296 0.1 -70.68123 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
E1 STOCK-1S-21278 500 -705.39462 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
E10 STOCK-1S-21287 0.03 -45.25220 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
E11 STOCK-1S-21288 0.01 -77.64494 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
E2 STOCK-1S-21279 300 -315.64286 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2
E3 STOCK-1S-21280 100 0.89749 % Inhibition 1 3 Alpha_Screen Plate 2 Plate 2

CHill60 25-Sep-15 10:17am

I've updated my solution with a fix for this problem . If you have any further problems I suggest that you open a new question as this has now become far too involved to follow properly

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-09-25T01:39:00

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.

VB

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

SQL

 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.

SQL

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

VB

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)

Member 12003400 · Accepted Answer · 2015-09-25T01:46:00

Solution 3

SQL

 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

Posted 25-Sep-15 1:46am

Member 12003400

Comments

CHill60 25-Sep-15 7:49am

Incorrect. Please see my solution - that is a list of column names not values. '500' is not a valid column but [500] is. Note also the error that will be produced by using AS pivot

Member 11819086 25-Sep-15 8:25am

Thank you for your response..

it is working but not in desired output form...

COMPOUND_NAME contains two times of the CONCENTRATION

7977797 NULL 351761.58499 NULL 508212.27179 457484.90318 384627.26038 212552.08840 387983.57205 474486.28549 377284.14203
STOCK-1S-21278 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
STOCK-1S-21279 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

but i need

7977797 ---->one row all contains pivot
7977797 ------>second row contains all pivot...

CHill60 25-Sep-15 8:29am

Sorry your problem is not clear

Member 11819086 25-Sep-15 8:48am

this is the desired output
COMPOUND_NAME 500 300 100 30 10 3 1 0.3
7977797 0.23 0.23 0254 0.25 0.65 .025 215 0.2
7977797 124 145 425 125 254 225 254 14

in case of compound code column contains pivoting valuesmore than two of each compound....

Member 11819086 25-Sep-15 8:50am

i am getting result like this

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

CHill60 25-Sep-15 9:01am

It might be best if you use the Improve Question link to post (some of) your raw data. I really cannot make out what you are trying to do.
I'm also surprised that you have accepted this Solution (Number 3) - it doesn't work and is completely incorrect.
Perhaps you would like to continue this conversation against my post after you have updated your question

Member 11819086 25-Sep-15 9:10am

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

Member 11819086 25-Sep-15 9:12am

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

CHill60 25-Sep-15 9:15am

That is not your source data. Nor is this the comments to my post.
You have also undone the accepted answer from both posts.
To be clear. Solution number 2 is my post - it is correct and works with the information you have provided.
Solution number 3 is the post that these comments are being added to. It is wrong.
Use the Improve question link to add your RAW data to your post along with your expected results.
Also post the code you are now using as the pivot I posted should not be showing these results

Member 11819086 25-Sep-15 9:23am

thank,,

please correct the querry

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us