CODEWITHSUNDEEP
sql
E West
E West

Reputation: 15

SQL - grouping with non-nulls

Need help coming up with a SQL query. Specifically, flattening into single rows.

Table columns: 

Unit, StartDate, MoveDate, NextDate, AfterDate, IncidentId

Data Rows

E11, 1/1/2010, null, null, null, F1
E11, null, 1/1/2011, null, null, F1
E11, null, null, 1/1/2012, null, F1
E12, 1/1/2011, null, null, null, F1
E12, null, 1/2/2013, null, null, F1

My goal is: 

E11, 1/1/2010, 1/1/2011, 1/1/2012, null, F1
E12 1/1/2011, 1/2/2013, null, null, F1

I thought a simple Group By would work: 

SELECT Unit, StartDate, MoveDate, NextDate, AfterDate, IncidentId 
FROM UnitTable
WHERE IncidentID = 'F1' 
GROUP BY Unit, StartDate, MoveDate, NextDate, AfterDate, IncidentId

but of course I get everything not on 1 line.

I think it should be a simple SQL but cant figure it out

Upvotes: 0

Views: 38

Answers (2)

Ed Callahan
Ed Callahan

Reputation: 189

You don't want to GROUP BY all your fields, that is basically just a SELECT DISTINCT. GROUP the fields you want to aggregate to, and take the max of the rest.

drop table if exists #temp
go

select *
    into #temp
    from (
        values
         ('E11', '1/1/2010', null, null, null, 'F1')
        ,('E11', null, '1/1/2011', null, null, 'F1')
        ,('E11', null, null, '1/1/2012', null, 'F1')
        ,('E12', '1/1/2011', null, null, null, 'F1')
        ,('E12', null, '1/2/2013', null, null, 'F1')
    ) t(Unit, StartDate, MoveDate, NextDate, AfterDate, IncidentId )

SELECT Unit, max(StartDate) StartDate, max(MoveDate) MoveDate, Max(NextDate) NextDate, Max(AfterDate) AfterDate, Max(IncidentId) IncidentId
    FROM #temp
    GROUP BY Unit

Upvotes: 1

Gauravsa
Gauravsa

Reputation: 6528

You can use this:

SELECT Unit, max(StartDate) StartDate, max(MoveDate) MoveDate, Max(NextDate) NextDate, Max(AfterDate) AfterDate, Max(IncidentId) IncidentId
FROM UnitTable
WHERE IncidentID = 'F1' 
GROUP BY Unit, StartDate, MoveDate, NextDate, AfterDate, IncidentId

Upvotes: 1

Related Questions