Select columns from left to right is > 0

In the sql below it is reading about 2000 records.
Is there any way to read those columns from left to right
Basically I want to choose column 1 if its > 0
If not then column 2
and so on

Just seems like I have a mess here



SELECT RollaPoll_IndividualID ,
            CASE WHEN [1] > 0 THEN [1]
                 WHEN [2] > 0 THEN [2]
                 WHEN [3] > 0 THEN [3]
                 WHEN [4] > 0 THEN [4]
                 WHEN [5] > 0 THEN [5]
                 WHEN [6] > 0 THEN [6]
                 WHEN [7] > 0 THEN [7] ELSE [8] END IndividualID
     FROM   #Results r

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
do you mean this?

case when column1 > 0 then column1 else column2 end yourResult

Open in new window

0
PortletPaulfreelancerCommented:
It's a mess because SQL is "row oriented" not column oriented. Sounds like you should "unpivot" you data first, then it will be way easier.

Pity you didn't provide any sample data, then we could show you much more.

See: UNPIVOT

or, an alternative:
https://www.experts-exchange.com/articles/12316/Unpivotting-by-CROSS-APPLY-and-VALUES.html
1
awking00Information Technology SpecialistCommented:
How many columns are in your table and are they all numeric?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott PletcherSenior DBACommented:
CASE is the standard method for doing what you want to do.  The pattern you initially posted is correct.
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
Larry Bristersr. DeveloperAuthor Commented:
Hey guys,
 There are the columns in my original question
RollaPoll_IndividualID
then [1] through [8]
0
PortletPaulfreelancerCommented:
Hey, you have the data, we don't.

CREATE TABLE Table1
    ([RollaPoll_IndividualID] int, [1] varchar(1), [2] varchar(1), [3] varchar(1), [4] varchar(1), [5] varchar(1), [6] varchar(1), [7] varchar(1), [8] varchar(1))
;
    
INSERT INTO Table1
    ([RollaPoll_IndividualID], [1], [2], [3], [4], [5], [6], [7], [8])
VALUES
    (1234, 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h')
;

Open in new window

SELECT RollaPoll_IndividualID, rowno, response  
FROM (  
       SELECT RollaPoll_IndividualID, [1], [2], [3], [4], [5], [6], [7], [8]  
       FROM table1
    ) p  
UNPIVOT (  
       response FOR rowno IN   
          ([1], [2], [3], [4], [5], [6], [7], [8])  
        ) AS unpvt
;  
 

Open in new window

| RollaPoll_IndividualID | rowno | response |
|------------------------|-------|----------|
|                   1234 |     1 |        a |
|                   1234 |     2 |        b |
|                   1234 |     3 |        c |
|                   1234 |     4 |        d |
|                   1234 |     5 |        e |
|                   1234 |     6 |        f |
|                   1234 |     7 |        g |
|                   1234 |     8 |        h |
 
also see http://sqlfiddle.com/#!18/690da/1       

Open in new window


1. You will get faster, more accurate, responses if you provide a small sample of your data, in such a way that we can parse it to build tables.
2. Also provide the "expected result" that should be produced by the wanted query.
0
Larry Bristersr. DeveloperAuthor Commented:
Sorry for the extreme delay getting back guys.

Was on extended trip for my 47th anniversary
0
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
SQL

From novice to tech pro — start learning today.