MS SQL Server first column with no zero

Larry Brister
Larry Brister used Ask the Experts™
on
I have select statement the produces 9 columns

What I need returned is for columns 2 through 9
The results of the first column where the integer value > 0

example... the columns would be
IndividualID       [1]         [2]         [3]  ... etc
95487512         32546      0         33365
95487513             0         33358  33365

The desired final select would be
IndividualID        OPCol
95487512            32546
95487513            33348
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Commented:
A CASE statement would work


SELECT IndividualID
	, CASE 
		WHEN Column1 > 0 THEN Column1
		WHEN Column2 > 0 THEN Column2
		WHEN Column3 > 0 THEN Column3
                ... etc....
	        WHEN Column9 > 0 THEN Column9
          END AS OPCol
FROM  YourTable

Open in new window


As an aside, not sure what the numbers represent, but seeing this kind of query suggests the table might benefit from restructuring and converting columns into rows (like the final result)
Larry Bristersr. Developer

Author

Commented:
yeah... thats what I thought... was hoping there was some super-secret wonderful thing that was recently invented
Larry Bristersr. Developer

Author

Commented:
Perfect... tanks
Most Valuable Expert 2015

Commented:
>> was hoping there was some super-secret wonderful thing that was recently invented
I don't think so, though I'm curious too :-)  ... but I suspect the multiple columns is going to necessitate a CASE or UNION ALL approach no matter what you do.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial