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.

The word you are looking for is `pivot` – ajeh Sep 29 '16 at 17:37 — ajeh, Sep 29 '16 at 17:37

trincot · Answer 1 · 2016-09-29T19:25:15.097

0

You could use this query:

select *
from   (
        select   date, noon as speaker, count(*) as times
        from     events
        group by date, noon
        union all 
        select   date, [3pm], count(*)
        from     events
        group by date, [3pm]
       ) as u
pivot  (
        sum(times)
        for speaker in ([Dick], [Tom], [Suzy])
       ) as piv
order by date desc;

... which gives you a count per cell (null, 1 or 2):

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

edited Sep 29 '16 at 19:25

answered Sep 29 '16 at 16:11

trincot

317,000
35
244
286

Thank you for your response. I actually need a count since some speakers speak twice on the same day. I also have something like 50 speakers over a 5 year period, some of whom are unique to their time slot which makes unions harder since there are different columns if you pivot each time slot individually. – anoddexperiment Sep 29 '16 at 16:29
OK, I updated the query to do a count. The `group by` clauses are necessary when multiple records can have the same date. If this is not necessary, you could optionally remove them, and replace `count(*)` with `1`. But if you have an unknown number of speakers I would suggest you use the capabilities of your programming environment. That is more suited for that. – trincot Sep 29 '16 at 19:29

score 0 · Answer 2 · answered Sep 29 '16 at 17:49

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)

Crosstab query in SQL that compares and adds columns

2 Answers2

Linked