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

You would do it the other way:

SELECT [Date], [Name], COUNT(*)
  FROM ...
  GROUP BY [Date], [Name];

Then perhaps you could pivot, but you don't necessarily have to do that in the query. Doing so when not knowing the names you have (and therefore the number of columns) will require dynamic SQL to construct a proper pivot - but again, you can transpose this information at the presentation layer and let SQL Server return this data the way it's optimized to do.

DECLARE @date date = '20130701';

DECLARE @sql  nvarchar(max) = N'', 
        @cols nvarchar(max) = N'';

SELECT @cols = STUFF((SELECT N',' + QUOTENAME(Name)
               FROM dbo.LoginData
               WHERE [Date] >= @date
               GROUP BY Name
               FOR XML PATH(''),
               TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, '');

SET @sql = N'SELECT *
  FROM (SELECT * FROM dbo.LoginData AS d
  WHERE [Date] >= @date
  ) AS d
  PIVOT (COUNT([Name]) FOR [Name] IN (' + @cols + ')) AS p;';

EXEC sys.sp_executesql @sql, N'@date date', @date;

• Example db<>fiddle