Select the Column Name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select the Column Name

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2010-08-06 : 01:22:20

Dear Friends,

I have the table called ReferenceInfo, Which contains the below sample value

ReferenceId UgStudent PgStudent Staff Lecturer Professor CanteenStaff
1 0 0 2 0 0 0
2 4 0 0 0 0 0
3 0 0 0 12 0 0

So in this table any of one single column will have the value other than 0, and the rest of the column will have 0.

I will pass the ReferenceID and have to get the column name which is having the value, Condition is all other column should have the value as 0.

For example if am passing the reference id = 2 I need to get the output as "UgStudent"

if am passing the reference id = 3 I need to get the output as "Lecturer"

Please help me to get the expected output.

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-06 : 01:27:16

be clear what you want the result

With Regards
Kashyap M

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-06 : 01:35:35

[code]
That is really the wrong way.

You should have one column named JOB_ID or whatever.
This column should have the values 1,2,3,4,5 or 6.

Another Table should hold
JOB_ID Description
1 UgStudent
2 PgStudent
3 Staff
...
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

dhinasql
Posting Yak Master

195 Posts

Posted - 2010-08-06 : 01:46:30

Thank you for your reply.

I dont have another table.

My Expected output
Ex 1 :
Where ReferenceID = 2
ColumnName
-----------
UgStudent

Ex 2 :
Where ReferenceID = 3
ColumnName
-----------
Lecturer

Ex 3 :
Where ReferenceID = 1
ColumnName
-----------
Staff

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-06 : 01:53:54

[code]select

case
when UgStudent <> 0 then 'UgStudent'
when PgStudent <> 0 then 'PgStudent'
when Staff <> 0 then 'Staff'
when Lecturer <> 0 then 'Lecturer'
when Professor <> 0 then 'Professor'
when CanteenStaff <> 0 then 'CanteenStaff'
else 'not found'
end as ColumnName

from your_table
where ReferenceID = your_passed_id
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources