Retrieve Column name based on criteria in select statement

Hello all:

I  have a table that has columns similar to the following:

 emp table
What I am trying to do is write a query that will get the column name based on the value of the field.  So for example, the my criteria is A Desc1 will be returned.  If the criteria is not represented in any of the columns, the query will return nothing.  Is this doable, and if so, can someone point me in the right direction?

Many thanks!

LVL 15
Juan OcasioApplication DeveloperAsked:
Who is Participating?
Mark WillsTopic AdvisorCommented:
Maybe this goes closer
;with cte as
  SELECT empname, JobType,col  
  FROM (SELECT empname, desc1, desc2, desc3 FROM emptable) p  
  UNPIVOT (JobType FOR col IN (desc1, desc2, desc3)  )AS u1                 --- include all the columns
) select empname, JobType, col 
  from cte
  where empname = 'Doe' and JobType = 'A'

Open in new window

Of course there needs to be the extra columns to include...
JLankfordIT Data AnalystCommented:
I used to do this years ago in MS Access VBA and in ADO.NET, but I have not directly in T-SQL. I did find a bit of reading on the topic though.

This *might* point you in the right direction.
Nitin SontakkeDeveloperCommented:
You have a given a rather too simplistic an example. Or may be many other aspects should be disclosed at the beginning, which is not done.

May be should have given few more rows and examples. I am almost certain that you are referring to finite number of dimentions here, 3 in your example. You can try something like as follows, assuming I have understood the requirement correctly and there are no more small prints:

declare @criteria varchar(10) = 'A';

select t.[EmpName], coalesc(t1.[Desc1], t2.[Desc2], t3.[Desc3]) [Desc], coalesc(t1.[Pay1], t2.[Pay2], t3.[Pay3]) [Pay]
from [dbo].[table_name] t
left outer join [dbo].[table_name] t1 on t.[EmpName] = t1.[EmpName] and t1.[Desc1] = @criteria
left outer join [dbo].[table_name] t2 on t.[EmpName] = t2.[EmpName] and t2.[Desc2] = @criteria
left outer join [dbo].[table_name] t3 on t.[EmpName] = t3.[EmpName] and t3.[Desc3] = @criteria

Open in new window

DISCLAIMER: I have not tested the code. Let me know if not working as expected.
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Mark WillsTopic AdvisorCommented:
Looks like a pivot result - how do you arrive at those column names as they currently appear ?


What will happen if more than 1 row ?

Lets say row 2 is 'John', 'Q', 12.50, 'R', 11.50, 'S', 10.10

We cant have the column desc1 become [A desc1] as you would have liked.

If not more than 1 row, then what do we leave in the row ? Or are we just moving the 'A', 'B', 'C', to column headings ?

Maybe a complete picture of the result set would help :)
Vitor MontalvãoMSSQL Senior EngineerCommented:
Juan, can you give a more complete example please?
With only a sample line we don't have a full view of the possible variants of your problem so our solutions may not cover all cases that you need.
Juan OcasioApplication DeveloperAuthor Commented:
Hello all:

Thank you for the reply.  To answer a few questions, this is similar to an existing table we have.  Sadly when first implemented it was not properly normalized, where the descriptions along with the pay would be in another table, and even more sad is the fact that so many other tables, and more importantly our current application that depends on this table cannot be modified easily to accommodate a new structure.

Soooo, with that in mind here is what I am trying to do: we currently have a utility that utilizes this table to determine an employee's rate based on the description provided to the utility. An employee can have multiple pay rates, hence the various descriptions.  so for example, if description entered into the utility is A, I want the payrate of 10.53 to be displayed.  And in all actuality, I'm going to write that information to another table. As mark described above, it is quite plausible for an employ to work 4 hours as A and 4 hours as B, so in the case of John, he would have two records:

John, A, 10.53
John, C, 1.05

I was also thinking a pivot for each record - what I will be doing is writing an update statement to update the payrate of each employee in the utility joining the temppay table on name and descrx, hence the reason for needing the column name - so if desc1 is where the code is, then pay1 is the value I need.  That part I can manage, I just need to figure out how to get the column name where the code is located for the record.

Sorry for the sparse information initially; it was late and I just wanted to get the question out there while I worked on it a bit.

Again, thanks for the help and support; it is truly appreciated!

Mark WillsTopic AdvisorCommented:
Why not union to normalise ?

Could make it a VIEW so you could select from the view.

Depends on how many column pairs there are - at the moment we know of three - are there more ?

For example (using "emptable" as the tablename)
select empname,desc1 as [desc], pay1 as pay from emptable  
union all
select empname,desc2 as [desc], pay2 as pay from emptable
union all
select empname,desc3 as [desc], pay3 as pay from emptable
order by 1,2,3

Open in new window

would give you the result set
empname	desc	pay
Doe 		A	10.53
Doe 		B	10.75
Doe 		C	1.05

Open in new window

Juan OcasioApplication DeveloperAuthor Commented:
Thank you Mark:

There are 16 different desc.  Sadly your solution won't work because Descr1 could be anything.  So for example for John his main payrate (descr1) may be A, however Jame's main payrate (desc1) could be B.  The descr columns are actually pay classes for a number of job types.
Mark WillsTopic AdvisorCommented:
OK, so what do you need ?

What is the relationship between the column titles (desc1 desc2 etc) and the values A or B or C

You say " if description entered into the utility is A, I want the payrate of 10.53 to be displayed."
does that mean we are trying to find the column title that matches 'A'

So 'A' is a pay class ?

What is the Job Type then and how does that relate to pay classes.


'A' is the job type, so I go find it in the table and it is in desc1 which means I use pay1

So, you would be supplying Empname, 'A', hours  and have to return Empname, 'A', desc1, pay1, (pay1 * hours)

Nearly there, just missing the link.
Juan OcasioApplication DeveloperAuthor Commented:
So the job a job type for the employee. So I’m basically saying if the utility enters a job type, find that job type for that specific employ and then use that pay rate. The desk directly correlates to the pay rate associated with the number. I can find the rate based on the desc column associated with it, I’m just looking for the column name for the job type selected on a per employee basis. Also note: A doesn’t relate to an actual hard pay rate. Emp 1 A job could fetch him 10.00 while emp2 A job can fetch her 15.00
Mark WillsTopic AdvisorCommented:
OK, so curiosity got the better of me and havent heard, so decided to play and came up with a stored procedure :
create procedure uspGetPay (@empname varchar(20), @JobType varchar(20), @hours int = 1)

   declare @sql varchar(max) = ''
   declare @payclass varchar(20)
   declare @payrate varchar(20)

   ;with cte as
     SELECT empname, JobType,col  
     FROM (SELECT empname, desc1, desc2, desc3 FROM emptable) p  
     UNPIVOT (JobType FOR col IN (desc1, desc2, desc3)  )AS u1
   ) select @payclass=col 
     from cte
     where empname = @empname and JobType = @jobtype

   set @payRate = replace(@PayClass,'Desc','Pay')   -- an oversimplification of deriving the pay column name from desc column name

   set @sql = 'select empname,'''+@jobtype+''' as JobType ,'''+@payclass+''' as PayClass,'''+@Payrate+''' as PayLevel,'+@Payrate+' as PayRate, '+cast(@Hours as varchar(10))+' as Hours,'+@PayRate+' * '+cast(@Hours as varchar(10))+' as Payment
from emptable
where empname = '''+@empname+''''

-- Print @sql

Open in new window

so, to use it, we simply call with empname, jobtype, hours and it will pump out the results
-- call the stored procedure

exec uspGetPay 'Doe','B',3

-- and the results

empname              JobType PayClass PayLevel PayRate               Hours       Payment
-------------------- ------- -------- -------- --------------------- ----------- ---------------------
Doe                  B       desc2    Pay2     10.75                 3           32.25

(1 row affected)

Open in new window

Would like to hear back, and understand that I might have totally missed the goal, but seemed to make sense to me...
Juan OcasioApplication DeveloperAuthor Commented:
Hey Mark:

thanks for the replies and assistance.  I will give both solutions a go today.  Got caught up in household stuff this past weekend and wasn't able to try.

Thanks again!

Juan OcasioApplication DeveloperAuthor Commented:
Thanks Mark!  This did the trick; greatly appreciate the time you took!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.