asked on

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

Ryan Chong

do you mean this?

case when column1 > 0 then column1 else column2 end yourResult

Open in new window

PortletPaul

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

awking00

How many columns are in your table and are they all numeric?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Brister

ASKER

Hey guys,
There are the columns in my original question
RollaPoll_IndividualID
then [1] through [8]

PortletPaul

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.

Larry Brister

ASKER

Sorry for the extreme delay getting back guys.

Was on extended trip for my 47th anniversary

Select columns from left to right is &gt; 0

Select columns from left to right is > 0