How do you fool the unknown?

Question

I ran into a problem when selecting an account in multiple columns using the CASE statement. How the CASE statement works for me, it's like the IF statement in C / C ++. IF the value is X, then YY ELSE do Z.

To help explain this problem, let me provide a query that counts the names in a column named "Names" and groups them by the "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 that I know the names that I want to count. What if I would like to consider names that are new and not defined in my query on one line? How can I dynamically search for all DISTINCT names in a table and read them separately as described above, without the need to add new names to the code?

Thanks for any help you can provide. I am still trying to learn various ways to use SQL for complex query writing. I am not looking for the exact answer, but any help to point me in the right direction would be wonderful. I all participate in the study and expansion of my knowledge, and not in the things that are given to me.

Answer 1

You would do it differently:

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

Then maybe you could collapse, but you don't have to do this in the request. If you don’t know the names that you have (and therefore the number of columns), you will need dynamic SQL to create the correct vertex - but again, you can transfer this information at the presentation level and let SQL Server return this data in the way It is optimized to do.

However, I am sure that bluefeet will appear soon to give an example.

Actually, the blue foot is gone, so I will write:

 DECLARE @date DATE = '2013-07-01'; DECLARE @sql NVARCHAR(MAX) = N'', @cols NVARCHAR(MAX) = N''; SELECT @cols += STUFF((SELECT ',' + QUOTENAME(Name) FROM dbo.LoginData WHERE [Date] >= @date GROUP BY Name FOR XML PATH('')), 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 sp_executesql @sql, N'@date DATE', @date; 

SQLfiddle demo

Answer 2

If you do not need all of them on one line, you can simply do:

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