How to select data where more than one column is populated

I have a sample table, called [SAMPLES] which I’ve added multiple columns to the end for different types of lab analysis results, however for the majority of samples only one analysis has been completed. I want to be able select only the samples where more than one analysis has been completed on the same sample.

The table structure is something like:

For the above, the query should select only samples ‘ABC122’ and ‘ABC123’.

Is there an easy way to select this data out?

• SQL 2012 database.

Answer

You can use cross apply and values to easily unpivot your columns back into the rows they should be, then count and filter accordingly

with x as (
    select distinct sampleId, SampleType, Count(*) over(partition by sampleid,sampletype) qty
    from samples s
    cross apply(values (analysis1),(analysis2),(analysis3),(analysis4),(analysis5),(analysis6))v(analysis)
    where analysis is not null
)
select s.*
from x join samples s on s.sampleid=x.sampleid and s.sampletype=x.sampletype
where x.qty>1