>Due to my beginner's knowledge of SQL Syntax, I am struggling with creating the following query to pass to SQL Server:
>
>Assume I have a simple table with 4 columns:
>
> workername
> Job 1 performed (0 or 1)
> Job 2 performed (0 or 1)
> Date entered
>
>with multiple entries for each name as work is performed on different days.
>
>I want a query which returns rows of 3 columns:
>
> workername
> Count of the number of times job 1 is performed (set to 1)
> Count of the number of times job 2 is performed (set to 1)
>
>with one row for each unique name.
>
>I understand how to do this if I only wanted one "count" column:
> SELECT workername , count(*) FROM WORKAUD where job1 = 1 group by workername
>
>But how can I include the count for job2 also?
>
>Thanks,
>Pat
This is done in SQL Server 2005 and up using Dynamic Pivot (if the names are not predefined). Simple google on "Dynamic Pivot SQL Server 2005" and you'll find tons of examples.
If it's not broken, fix it until it is.
My Blog