t-sql query

I have a table sql server 2008 table called MyTestTable1

Here is my table and script:
http://sqlfiddle.com/#!3/78d82/2

So my table looks like this:
my table
I need to write a query that does the following:

Look at columns MyCol1, MyCol2, MyCol3 and whichever of those columns does not equal to 0, use the value for the column from the detail column.
So in this case the column called MyCol3 doesn't not equal to zero. So then MyCol3 is the column with a value , so now instead of showing 6 I want to display the value in the detail column.

So my result set should be this:
desired result set
So for example if MyCol2 had been the one with the value of 6 then my output result would have been this:

test 2 of desired result
I started to write the query like this:

SELECT OrderId
             ,Case
         When ([MyCol1] <> 0) THEN [MyCol1Detail]
         When ([MyCol2] <> 0) THEN [MyCol2Detail]
         When ([MyCol3] <> 0) THEN [MyCol3Detail]
        ELSE End
FROM [MyTestTable1]


But I'm not sure I have the syntax correct or how to say name the resulting column whatever the name of the column with a value is?

can anyone help?
LVL 1
maqskywalkerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

That query looks like it will work - apart from the else in the case. take the word else out.

HTH
  David
0
maqskywalkerAuthor Commented:
Updating my question...

So i have something close to what I need.

This is my query ( http://sqlfiddle.com/#!3/78d82/4 ):

SELECT OrderID
      ,Case
             When ([MyCol1] <> 0) THEN [MyCol1Detail]
             When ([MyCol2] <> 0) THEN [MyCol2Detail]
             When ([MyCol3] <> 0) THEN [MyCol3Detail]
       End
FROM [MyTestTable1]
 
This gives me this

my current result
How do I add give the column name , the same name as the column that had a value.
So the name will either be MyCol1, MyCol2,  or MyCol3.

Since my table has value of 6 in MyCol3 the name of my output table show be called MyCol3
0
David ToddSenior DBACommented:
Hi,

SELECT OrderID
      ,Case
             When ([MyCol1] <> 0) THEN [MyCol1Detail]
             When ([MyCol2] <> 0) THEN [MyCol2Detail]
             When ([MyCol3] <> 0) THEN [MyCol3Detail]
       End as Detail
      ,Case
             When ([MyCol1] <> 0) THEN 'MyCol1'
             When ([MyCol2] <> 0) THEN 'MyCol2'
             When ([MyCol3] <> 0) THEN 'MyCol3'
       End as WhichColumn
FROM [MyTestTable1]

Regards
  David
0
maqskywalkerAuthor Commented:
David,

 in the the first case statement
Can I replace End As Detail

with End AS ( Case
             When ([MyCol1] <> 0) THEN 'MyCol1'
             When ([MyCol2] <> 0) THEN 'MyCol2'
             When ([MyCol3] <> 0) THEN 'MyCol3'
       End)

Because I think that's what I need?
0
David ToddSenior DBACommented:
Hi,

Nope. What you are trying to do wont work.

It took me a while to think about this: What in effect you are trying to do is _rename_ the column depending on what the value on that row is. Can't do that. We can dynamically name the column for things like pivot tables if you're willing to live with some horrible looking code.

Or if you really want the columns to follow the sort of value then its really simple

select * from table

HTH
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.