In SQL Server lets say you have a table like this called "Testing":
| Owner | State |
|---|---|
| John | Pending |
| John | Complete |
| Sue | Required |
| Sue | Required |
| Sue | Complete |
| Frank | Complete |
I want the pivot data to appear as follows:
| Owner | Required | Pending | Complete |
|---|---|---|---|
| John | 0 | 1 | 1 |
| Sue | 2 | 0 | 1 |
| Frank | 0 | 0 | 1 |
How do you write the SQL statement to produce that? It seems the PIVOT table would come in handy but just not sure how to include that in the statement.
CodePudding user response：
Conditional aggregates as Tim supplied ( 1) offer a bit more flexibility and often have a performance bump
That said, here is a PIVOT option
Example or 
NOTE: If your table has more columns than presented, you would need a subquery to limit just the essential columns.
Here is a DYNAMIC version
Declare @SQL varchar(max) = '
Select *
From ( Select [State]
,[Owner]
From YourTable ) src
Pivot (count([State]) For [State] in (' Stuff((Select Distinct ',' QuoteName([State])
From YourTable A
Order By 1 Desc
For XML Path('')),1,1,'') ') ) p
Order By Owner'
--Print @SQL
Exec(@SQL)
CodePudding user response：
You may use the following pivot query:
SELECT
Owner,
COUNT(CASE WHEN Genre = 'Required' THEN 1 END) AS Required,
COUNT(CASE WHEN Genre = 'Pending' THEN 1 END) AS Pending,
COUNT(CASE WHEN Genre = 'Complete' THEN 1 END) AS Complete
FROM Testing
GROUP BY Owner;