Click here to Skip to main content
15,301,414 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi everyone,

I want sql query for the following thing.

My table is as follows :

Categorycode Category Marksthere Scalepoint
1 scholastic 0 5
2 co-scholastic 1 NULL
3 part -1 0 10


Now here is the table of category where fields
categorycode is the primary key
category is the varchar field
marksthere is the bit field which is always 0 or 1
scalepoint is the integer field

Now what i need is a query which results if there is Marks in the category i.e when the marksthere field is (1) then the scale point should not come in the resultant output
But if the marksthere column has no marks i.e. it is (0) then in the resultant output should have the scalepoint in it.
I need a SQL query for selecting all the fields....on the basis of Marksthere
For eg.

if Marksthere is 0
then the fields should be categorycode, category,marksthere,scalepoint
but when marksthere is 1
then the fields should be categorycode,category,marksthere

Please help me out for the thing i am really stucked up..
Thanks in advance..

Regards,
Krunal Panchal
Posted
Updated 16-Jun-11 2:59am
v3

I am not sure that you are going to bind the result in any data displaying control like GridView/Repeater or you are going to do some manipulation on the result.

But if you are going with first option then you will need to display 4 columns i.e CategoryId, CategoryCode, MarksThere, ScalePoint in the tabular format. So as per your required condition if MarksThere has 0 then ScalePoint should be displayed other wise not.

In that case you must need a header with ScalePoint. You can place dash, nil or any other suitable text in that column where MarksThere are 1.

So for this option you can simply write a query like below.

SELECT CategoryId, CategoryCode, MarksThere, CASE WHEN MarksThere = 0 THEN Convert(varchar,ISNULL(ScalePoint,0)) ELSE '-' END AS ScalePoint FROM TableName

Hope it helps.
   
you can do that by using the two seperate query

means u have to hit database twice to get the values..... if u r missing one col at one time.

if u want in 2 query, then i'll give u.
   
Comments
[no name] 16-Jun-11 8:58am
   
No i dont want it in two query i need it in one single query.. if you have solution please help me ..
What you are asking is not possible in SQL. An SQL Query returns a fixed number of fields/columns. A Query cannot return one row of a result with four columns, and then the next row of the same resultwith two columns. I hope you can understand what I am trying to say.

Sorry.
   

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900