Using dates in field to create new columns in SQL Server

Question

I'm very new to SQL. I have constructed a query such that each row designates the occurrence of an event. Here is a sample (this is stored in a temporary table in the query).

Id  Date        Acct    FirstName   LastName
--------------------------------------------
123 2016-02-02  456789  Joan        Smith
123 2016-02-03  456789  Joan        Smith
456 2016-02-02  987654  John        Adams
789 2016-02-04  123789  Matt        James
789 2016-02-07  123789  Matt        James

What I'm looking to do is take that table and count the total number of times the event occurred for each person, but also have the ability to see which dates the event occurred on.

So the final table should be something like this:

ID  Acct    FirstName   LastName    2/1/2016    2/2/2016    2/3/2016    2/4/2016    2/5/2016    2/6/2016    2/7/2016    Total
123 456789  Joan        Smith                          1           1                                                        2
456 987654  John        Adams                          1                                                                    1
789 123789  Matt        James                                                  1                                   1        2

I am able to get a total number of occurrences, but I don't know how to get the dates as new columns. Would CASE work? Or PIVOT? I'm not sure how I would use those two commands to accomplish this.

Here is the query that I have constructed so far and the output

select 
    ID,
    Acct,
    FirstName,
    LastName,
    Count(Date) as Total
from 
    ##tempTable
group by 
    Acct, PlayerID, FirstName, LastName

Output:

ID  Acct    FirstName   LastName    Total
-----------------------------------------
123 456789  Joan        Smith       2
456 987654  John        Adams       1
789 123789  Matt        James       2

Assuming you want the dates populated dynamically, this would require dynamic SQL to create the query based on your temporary table. — Andrew O'Brien, Mar 11 '16 at 20:50
Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Tab Alleman, Mar 11 '16 at 20:58

score 0 · Accepted Answer · answered Mar 11 '16 at 20:31

0

If you only care about those days, following should work

select ID,
Acct,
FirstName,
LastName,
SUM(CASE WHEN [DATE] = '2016-02-01' THEN 1 ELSE NULL END) AS '2/1/2016',
SUM(CASE WHEN [DATE] = '2016-02-02' THEN 1 ELSE NULL END) AS '2/2/2016',
SUM(CASE WHEN [DATE] = '2016-02-03' THEN 1 ELSE NULL END) AS '2/3/2016',
SUM(CASE WHEN [DATE] = '2016-02-04' THEN 1 ELSE NULL END) AS '2/4/2016',
SUM(CASE WHEN [DATE] = '2016-02-05' THEN 1 ELSE NULL END) AS '2/5/2016',
SUM(CASE WHEN [DATE] = '2016-02-06' THEN 1 ELSE NULL END) AS '2/6/2016',
SUM(CASE WHEN [DATE] = '2016-02-07' THEN 1 ELSE NULL END) AS '2/7/2016',
Count(Date) as Total
from ##tempTable
group by Acct, ID, FirstName, LastName

answered Mar 11 '16 at 20:31

xbb

2,073
1
19
34

This is something that's going to be run weekly, but I'm okay with just changing the dates as necessary. Since I limit the date range earlier in the query using a 'where Date betwee x and y' statement, would it be possible to do something like min(date) max (date) and then everything in between? If not, oh well. Like I said, I'm okay just changing the dates as necessary. – Mike Edinger Mar 11 '16 at 21:45
@MikeEdinger In that case, you might want to wrap this query into a string , along with your defined min, max date. Then execute use EXEC(@yourquery). It's definitely doable, but whole lot of complication as far as I can think of. – xbb Mar 14 '16 at 12:45

Using dates in field to create new columns in SQL Server

1 Answers1