CODEWITHSUNDEEP
sql-server
LucasC922
LucasC922

Reputation: 169

SQL Server: Consolidate the result into a single row

I have the following data result.

Name      Shift   Station   0-4 Hrs    5-12 Hrs    13-18 Hrs    19-24 Hrs   TotalPayAmount
John Doe   A       02        1           0           0             0             0
John Doe   A       02        0           1           0             0             15
John Doe   A       02        0           0           1             0             20

I need the result to look like this.

Name      Shift   Station   0-4 Hrs    5-12 Hrs    13-18 Hrs    19-24 Hrs   TotalPayAmount
John Doe   A       02        1           1           1             0             35

I think I have to use the PIVOT function, but I just do not know how to.

Here is the SQL query with an example user "John Doe", but there are many users.

select s.name
,s.shift
,s.station
,case
when t.WorkHours <= 4 then count(*) else 0
end as '0-4Hrs'
,case 
when t.WorkHours >= 5 and t.WorkHours <= 12 then count(*) else 0
end as '5-12Hrs'
,case 
when t.WorkHours >= 13 and t.WorkHours <= 18 then count(*) else 0
end as '13-18Hrs'
,case
when t.WorkHours >= 19 and t.WorkHours <=24 then count(*) else 0
end as '19-24Hrs'
,sum(p.PayRate) as TotalPayAmount
from staff s
left join Time t on t.FSID = s.FSID
left join Pay p on p.Hours = t.WorkHours
where s.name = 'John Doe'
group by  s.name, s.shift, s.station, t.WorkHours
order by s.shift, s.name

Upvotes: 1

Views: 40

Answers (1)

Thom A
Thom A

Reputation: 95554

I think this what you need. Notice I move the CASE expression to be inside the COUNT to apply to "pivot" (also known as conditional aggregation):

SELECT s.[name],
       s.shift,
       s.station,
       COUNT(CASE WHEN t.WorkHours <= 4 THEN 1 END) AS [0-4Hrs],
       COUNT(CASE WHEN t.WorkHours >= 5 AND t.WorkHours <= 12 THEN 1 END) AS [5-12Hrs],
       COUNT(CASE WHEN t.WorkHours >= 13 AND t.WorkHours <= 18 THEN 1 END) AS [13-18Hrs],
       COUNT(CASE WHEN t.WorkHours >= 19 AND t.WorkHours <= 24 THEN 1 END) AS [19-24Hrs],
       SUM(p.PayRate) AS TotalPayAmount
FROM dbo.staff s
    LEFT JOIN dbo.[Time] t ON t.FSID = s.FSID
    LEFT JOIN dbo.Pay p ON p.Hours = t.WorkHours
WHERE s.[name] = 'John Doe'
GROUP BY s.[name],
         s.shift,
         s.station
ORDER BY s.shift;

Upvotes: 2

Related Questions