How to combine multiple SQL queries on one table into one report

user_13052410 0
Viewed 70

I have looked at subqueries, unions, and joins. They don't seem to be able to produce what I am looking for, or maybe I am not using them right.

I'm using SQL Server 2016.

Here is what I have. One table (StoreScan) with a row for store number and then a bunch of other rows with collected data on each store.

Example: 

StoreNumber, uptime, service1status, service2status

Example data:

Storenumber     uptime    service1status    service2status  
-----------------------------------------------------------
 1              10        Running           Running
 18             25        Running           Stopped 
 88             3         Stopped           Running
 90             1         Running           Running
 103            5         Stopped           Running
 553            2         Running           Stopped
 989            2         Running           Running

I have several small queries like this: 

select storenumber, uptime 
from storescan 
where uptime > 7


select storenumber, service1status 
from storescan 
where service1status = 'stopped'

I'd like to combine them all into one query for a report that would look something like this:

Storenumber     uptime    service1status    service2status  
-----------------------------------------------------------
1               10  
18              25  
88                        stopped  
103                       stopped  
553                                         stopped  
18                                          stopped
report sql-server
1 Answers

Do conditional aggregation : 

select Storenumber, 
       max(case when uptime > 7 then uptime end) as uptime,
       max(case when service1status = 'stopped' then service1status end),
       max(case when service2status = 'stopped' then service2status end)
from storescan ss 
group by Storenumber;
user_55294450
Related