How do you CASE out the unknowns?

Question

I ran into a problem when selecting a count into multiple columns with a CASE statement. How the CASE statement works for me is like a IF statement in C/C++. IF the value equals X, then do Y ELSE do Z.

To help explain this problem, let me provide a query that counts names within a column called 'Names' and groups them by a 'Date' column.

SELECT [Date]
       COUNT( CASE WHEN [Name] = 'John' THEN 1 ELSE NULL END) AS 'John',
       COUNT( CASE WHEN [Name] = 'Joe' THEN 1 ELSE NULL END) AS 'Joe',
       COUNT( CASE WHEN [Name] = 'Moe' THEN 1 ELSE NULL END) AS 'Moe',
       COUNT( CASE WHEN [Name] = 'Nick' THEN 1 ELSE NULL END) AS 'Nick',
       COUNT( CASE WHEN [Name] = 'Zack' THEN 1 ELSE NULL END) AS 'Zack'
FROM [MyDatabase].[dbo].[LogInData]
WHERE [Date] >= '2013-07-01'
GROUP BY [Date]

This assumes I know the names I want to count. What if I wanted to count the names that are new and not defined in my query IN a single row? How can I make this dynamically search for all DISTINCT names in a table and count them separately as above automatically without having to adding new names to the code?

Thanks for any help you can provide. I'm still trying to learn different ways to utilize SQL for complex query writing. I'm not looking for an exact answer, but any help to point me in the right direction would be great. I'm all for learning and expanding my knowledge as opposed to things given to me.

Answer 1

If you don't need them all in a single row, then you can just do:

SELECT [Date],
       [Name],
       COUNT(*)
FROM [MyDatabase].[dbo].[LogInData]
WHERE [Date] >= '2013-07-01'
GROUP BY [Date],[Name]