Case Statement

Question

Post reply

Case Statement

rk1980factor

SSCommitted

Points: 1681
More actions
May 5, 2016 at 8:27 am

#316284

Hello,
I am trying to write simple case statement based on attached spreadsheet. please see the attached document. It is both in xlsx and zip format.
Here is the copy paste of spreadsheet but if the format get messed up you can check out the attachment:
STD_NOGRADECLASSDISTRICTAREA CityZip
3 2 H1 0goe 053423
3 1 H3 0hei 043233
3 3 H5 0wki 023534
5 0 h4 west0 arlington0
what i am looking for is if the Grade=0 than display the record with district and city and if the Garde is NOT ZERO than display the record with with Area and zip.
please let me know if you have any questions

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

rk1980factor SSCommitted Points: 1681 More actions · Answer 1

i mention case statement in subject line , but it can be if statement or case or anything which is easier to use

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

rk1980factor (5/5/2016)
Hello,
I am trying to write simple case statement based on attached spreadsheet. please see the attached document. It is both in xlsx and zip format.
Here is the copy paste of spreadsheet but if the format get messed up you can check out the attachment:
STD_NOGRADECLASSDISTRICTAREA CityZip
3 2 H1 0goe 053423
3 1 H3 0hei 043233
3 3 H5 0wki 023534
5 0 h4 west0 arlington0
what i am looking for is if the Grade=0 than display the record with district and city and if the Garde is NOT ZERO than display the record with with Area and zip.
please let me know if you have any questions

Something like this?

select t1.STD_NO

,t1.GRADE

,t1.CLASS

,DISTRICT = iif(t1.Grade = 0, t1.District, t1.Area)

,AREA = iif(t1.Grade = 0, '0', t1.Area)

,City = iif(t1.Grade = 0, t1.City, '0')

,Zip = iif(t1.Grade = 0, '0', t1.Zip)

from t1;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

rk1980factor SSCommitted Points: 1681 More actions · Answer 3

that doesn't seem to be working

name of the table is "test"

Michael L John One Orange Chip Points: 27060 More actions · Answer 4

Well, you could take the code that was provided and change the name from "t1" to "test" and try to figure it out.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

rk1980factor SSCommitted Points: 1681 More actions · Answer 5

Just to clarify little bit more:

1) i want to display all the records

2) but I want create new column says School which has data from either district or area. So if Grade =0 than school column will have district's data, if the Grade is NOT 0 than school column will have Area data

3) Similary I want to crate new column Zone which has data from either city or zip. So if Grade=0 than zone will have City's data and if the Grade is NOT 0 than Zone will have Zip's data.

Remember the Condition is applied one time for e.g. Grade is 0 or not so need to check everything in one statement.

Michael L John One Orange Chip Points: 27060 More actions · Answer 6

What have you tried? Can you post this?

Also, we cannot see your data or your schema. Can you follow the link in my signature so we can properly help you?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Phil Parkin SSC Guru Points: 246985 More actions · Answer 7

rk1980factor (5/5/2016)
Just to clarify little bit more:
1) i want to display all the records
2) but I want create new column says School which has data from either district or area. So if Grade =0 than school column will have district's data, if the Grade is NOT 0 than school column will have Area data
3) Similary I want to crate new column Zone which has data from either city or zip. So if Grade=0 than zone will have City's data and if the Grade is NOT 0 than Zone will have Zip's data.
Remember the Condition is applied one time for e.g. Grade is 0 or not so need to check everything in one statement.

Clarify? You mean, change.

Here is your original desired output, containing no columns called School or Zone. Why did you post this when what you wanted was something different?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel