Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

t-sql query

Posted on 2014-02-28
5
304 Views
Last Modified: 2014-03-01
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?
0
Comment
Question by:maqskywalker
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39896440
Hi,

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

HTH
  David
0
 
LVL 1

Author Comment

by:maqskywalker
ID: 39896443
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39896448
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
 
LVL 1

Author Comment

by:maqskywalker
ID: 39896464
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
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 39896608
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question