SQL Query for the following table

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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 16-Jun-11 2:08am

krunal25

Updated 16-Jun-11 2:59am

Wayne Gaylard

v3

Add a Solution

3 solutions

Solution 1

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.

Posted 16-Jun-11 2:36am

amit28august

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 ..

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)

^That's **Aragon** · Accepted Answer · 2011-06-16T03:06:00

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.

Wayne Gaylard · Accepted Answer · 2011-06-16T03:03:00

Solution 2

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.

Posted 16-Jun-11 3:03am

Wayne Gaylard