sql server - Crosstab query in SQL that compares and adds columns

Question

I have a table in sql server that contains three columns: "date", "noon", and "3pm." The first column is self-explanatory, but the latter two contain the names of guest speakers at a venue according to the time they arrived. I want to write a cross-tab query that writes speaker names into the column header and counts the number of times that speaker spoke on that date.

Example

Date   | Noon   |  3pm
092916 | Tom    | <null>
092816 | Dick   |  Tom 
092716 | <null> |  Suzy 

Desired Output

Date   | Dick   |  Tom   |  Suzy
092916 | <null> |   1    | <null> 
092816 |  1     |   1    | <null>
092716 | <null> | <null> |   1

I can do this pretty easily with a crosstab query if I only select one time and put a count into the value category, but I'm having trouble with merging multiple times so that I can get an accurate count of who spoke on what day.

Answer 1

you can build your query dynamically.

this will create a count(case) statement for each name found in either the noon or 3pm column.. similar to COUNT(CASE WHEN 'Dick' IN ([Noon],[3pm]) THEN 1 END) as [Dick]

DECLARE @speakers NVARCHAR(MAX),
        @sql NVARCHAR(MAX)

SET @speakers = STUFF((
    SELECT  ',COUNT(CASE WHEN ''' + [Name] + ''' IN ([Noon],[3pm]) THEN 1 END) as ' + QUOTENAME([Name])
    FROM    (SELECT [Noon] AS [Name] FROM Table1
             UNION ALL SELECT [3pm] FROM Table1) t
    GROUP BY t.Name 
    FOR XML PATH('')
), 1, 1, '')

SET @sql = N'SELECT Date, ' + @speakers + ' FROM Table1 GROUP BY Date'

--Print @sql to see what's going on
EXEC(@sql)