SQL Server 2008 R2 – Reducing multiple rows into one

Question

I need to have my data showing in one row whether an individual has 'Finished' specific courses within the last 3 years.

This is my existing table & desired table (hope this link works as I can't post images yet!): https://i.stack.imgur.com/c8oJO.png

I have tried this code:

SELECT DISTINCT
EN,
(First_Name + ' ' + Last_Name) as Name,

First_Aid = CASE
WHEN Course = 'First Aid' 
and Status = 'Finished'
and Course_Start_Date > DATEADD(day, -1095, GETDATE())
THEN 'F'
ELSE 'Nope'
END,

Manual_Handling = CASE
WHEN Course = 'Manual Handling'
and Status = 'Finished'
and Course_Start_Date > DATEADD(day, -1095, GETDATE())
THEN 'F'
ELSE 'Nope'
END,

Fire_Safety = CASE
WHEN Course = 'Fire Safety'
and Status = 'Finished'
and Course_Start_Date > DATEADD(day, -1095, GETDATE())
THEN 'F'
ELSE 'Nope'
END
into MyTraining
FROM Learning_History`

This produces accurate results, but with a separate row for each CASE statement (plus an extra row with all “Nope” results). So I have tried this code:

SELECT 
EN,
(First_Name + ' ' + Last_Name) as Name,

First_Aid =MAX(CASE
WHEN Course = 'First Aid' 
and Status = 'Finished'
and Course_Start_Date > DATEADD(day, -1095, GETDATE())
THEN 'F'
ELSE 'Nope'
END),

Manual_Handling =MAX(CASE
WHEN Course = 'Manual Handling' 
and Status = 'Finished'
and Course_Start_Date > DATEADD(day, -1095, GETDATE())
THEN 'F'
ELSE 'Nope'
END),

Fire_Safety =MAX(CASE
WHEN Course = 'Fire Safety' 
and Status = 'Finished'
and Course_Start_Date > DATEADD(day, -1095, GETDATE())
THEN 'F'
ELSE 'Nope'
END)

into MyTraining
FROM Learning_History

Group By EN,First_Name, Last_Name

This code does produce 1 row - but all the results are 'Nope'.

I don't really understand much of this (just Googled for most of the code) as I'm very much a beginner with SQL (so I may have made some simple errors). I think pivot tables may be the way to go - but I can't get my head around them...

mxix · Accepted Answer · 2015-01-15T15:41:23.350

You can do it with PIVOT or case and group by like you are trying.

PIVOT Example:

select
    EN,
    [First Aid],
    [Manual Handling],
    [Fire Safety]
from (
    select
        [EN],
        [Course],
        [Status]
    from (
        select
            ROW_NUMBER() OVER (
                PARTITION BY
                    EN,Course
                ORDER BY
                    Course_Start_Date
            ) rID,
            *
        from (
            select
            *
            from (
                values
                    (1,'First Aid','1999-06-22','Finished'),
                    (1,'First Aid','2013-02-19','Finished'),
                    (1,'Manual Handling','2014-02-10','Cancelled'),
                    (1,'Manual Handling','2014-03-20','Finished'),
                    (1,'Fire Safety','2099-07-29','Finished'),
                    (1,'Fire Safety','2014-11-19','No Show')
            ) myTable ([EN],[Course],[Course_Start_Date],[Status])
        ) myTable
    )T
    where
        rID = 1
) S
PIVOT (
    MAX(Status) FOR Course IN ([First Aid],[Manual Handling],[Fire Safety])
) PVT

PS.: i'm assuming EN is the PRIMARY KEY

EDIT: Give it another try ,)

Thanks mxix. I don't understand much of this, but I got it to run ok. It doesn't seem to check the course start dates though. Also - it returns 41000 rows, when the original table has only 4000 rows for those 3 courses. Thanks tho' — ttratl, Jan 15 '15 at 15:35
Thanks mxix. Can't get this to work for me. I don't understand pivot tables so can't work out what's going on. Thanks for your help though. I have now found out that if I take out the "Else.." parts of my case statements it all works in one row. Not quite what I wanted - but will persevere with this code. — ttratl, Jan 21 '15 at 08:41

SQL Server 2008 R2 – Reducing multiple rows into one

1 Answers1