Need help with SQL select query.

Question

Need help with SQL select query.

john_sidney

SSC Journeyman

Points: 84
More actions
February 9, 2018 at 1:31 am

#333450

Hi,
I'm a bit stuck with how I best approach this so thought I'd run it by the experts.
I have a table where I'm expecting around 1 or 2 million rows. In that table, there are 20 columns of interest. Within those 20 columns, their value will be either a decimal value or a null.
I'm using Python to access the data and within Python, there's a function that gets a list of the returned columns. So, if you did 'select * from table', you could say get_returned_columns.
So what I want to do is return only the columns that have a decimal value (or not NULL). A subset of my table could look like this:
col1, col2, col3, col4, col5
--------------------------------------------------
1.1, NULL, 2.4, 1.2, NULL
NULL, NULL, 22.0, NULL, NULL
NULL, NULL, NULL, 1.1, NULL
I don't even know where to begin so I'm going to try this in plain english:
"Select only the columns where the values have at least one decimal value (or NOT NULL)"
My results would look like this (col2 and col5 contain only NULL):
col1, col3, col4
----------------------------
1.1, 2.4, 1.2
NULL, 22.0, NULL
NULL, NULL, 1.1
Hope that makes sense? I'm not very good with SQL so please ask if you need further info.
Cheers,
J

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You can't do that easily. You'd need one query to determine which columns contain values, and a second query to select only those columns. Maybe some sort of dynamic pivot would work, but I haven't really thought that through (it would be cumbersome). Or you could just return everything and have your presentation layer choose which ones to display.

John

Thom A SSC Guru Points: 99126 More actions · Answer 2

This would much more easily achieved using something like SSRS. You can put an expression on the column then, and hide it if the count of the values in the column are 0. Would be something like:
=Iif(Count(Col2.Value) = 0, TRUE, FALSE)

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

john_sidney SSC Journeyman Points: 84 More actions · Answer 3

Thanks to you both!

@john-2 - I was afraid of that. I was hoping there was a one-query solution but not the end of the world. Was trying to be flash 🙂

@thom-2 - I'm going to look into that as it does sound like a very good solution. Suspected I'd have to revert back to design but all for the good.

Thanks both! Appreciate the quick reply.

Cheers,
J