Something like this should do it.

SELECT

B.Location,

'EXEMPT' as 'Batch ID',

A.EmpId as 'File #',

B.EmpLastName as 'Last Name',

B.EmpFirstName as 'First Name',

'y' as 'Cancel Pay',

NULL as 'Pay #',

NULL as 'Reg Earnings',

2 AS 'Rate Code',

NULL as 'Tax Frequency',

NULL as 'Reg Hours',

case when SUM(CASE WHEN Type = 'Sick' THEN A.Total_Hours end) IS not null then 'S' end as 'Hours 4 Code',

SUM(CASE WHEN Type = 'Sick' THEN A.Total_Hours end) as 'Sick Hours',

case when SUM(CASE WHEN Type = 'Vacation' THEN A.Total_Hours end) IS not null then 'V' end as 'Hours 4 Code',

SUM(CASE WHEN Type = 'Vacation' THEN A.Total_Hours end) as 'Vacation Hours',

case when SUM(CASE WHEN (Type != 'Vacation' AND Type != 'Sick') THEN A.Total_Hours end) is not null then 'O' end as 'Hours 4 Code',

SUM(CASE WHEN (Type != 'Vacation' AND Type != 'Sick') THEN A.Total_Hours end) as 'Unknown Hours'

FROM #tblTimeOffRequest A

INNER Join #tblEmployee B ON A.EmpId = B.EmpId

WHERE From_Date between @FromDate AND @ToDate

AND Location = @Loc

AND PayType = 'S'

GROUP BY Location, A.EmpId, EmpLastName, EmpFirstName