Help Summarizing Data in SQL

Hello Experts,

I need to summarizing data to look like crosstab in ms access.  My data is on MS SQL server.  I have attached a sample data and what i want the data to look like.  Can someone please help me with a sql code?

userid      calldate      5/1/2015      6/1/2015      7/1/2015
100510      5/1/2015      5      2      6
100436      5/1/2015      2      3      1
100577      6/1/2015            4      11
100933      6/1/2015            12      24
101397      7/1/2015                  1

Thank you very much
sample.csv
LVL 1
fb1990Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
What you are asking for looks like a "dynamic pivot". i.e. you want to pivot the data (rows into columns) and the column headings will change which requires dynamic sql.

What SQL Sever version are you using? (it's always a good idea to include version)

btw: Using code blocks make life easier
userid,calldate,channel
100933,07/01/2015,phonebank
100577,07/01/2015,phonebank
100933,07/01/2015,phonebank
100510,06/01/2015,phonebank
100510,06/01/2015,phonebank
100933,06/01/2015,phonebank
100436,06/01/2015,phonebank
100436,05/01/2015,phonebank
100436,05/01/2015,phonebank
100577,07/01/2015,phonebank
100577,06/01/2015,phonebank
100933,06/01/2015,phonebank
100933,06/01/2015,phonebank
100510,06/01/2015,phonebank
100436,05/01/2015,phonebank
100436,05/01/2015,phonebank
100933,07/01/2015,phonebank
100577,07/01/2015,phonebank
100577,07/01/2015,phonebank
100933,07/01/2015,phonebank
100577,07/01/2015,phonebank
100933,07/01/2015,phonebank
100933,07/01/2015,phonebank
100933,07/01/2015,phonebank
100510,07/01/2015,phonebank
100933,07/01/2015,phonebank
100577,06/01/2015,phonebank
100933,06/01/2015,phonebank
100577,06/01/2015,phonebank
100577,06/01/2015,phonebank
100436,06/01/2015,phonebank
100933,06/01/2015,phonebank
100933,06/01/2015,phonebank
100510,05/01/2015,phonebank
100436,05/01/2015,phonebank
100933,07/01/2015,phonebank
101397,07/01/2015,phonebank
100933,07/01/2015,phonebank
100933,07/01/2015,phonebank
100933,07/01/2015,phonebank
100436,07/01/2015,phonebank
100933,07/01/2015,phonebank
100436,07/01/2015,phonebank
100933,07/01/2015,phonebank
100933,07/01/2015,phonebank
100933,07/01/2015,phonebank
100577,07/01/2015,phonebank
100577,07/01/2015,phonebank
100933,07/01/2015,phonebank
100933,07/01/2015,phonebank
100436,07/01/2015,phonebank
100436,07/01/2015,phonebank
100436,07/01/2015,phonebank
100436,07/01/2015,phonebank
100933,07/01/2015,phonebank
100933,07/01/2015,phonebank
100933,07/01/2015,phonebank
100577,07/01/2015,phonebank
100577,07/01/2015,phonebank
100577,07/01/2015,phonebank
100933,07/01/2015,phonebank
100933,07/01/2015,phonebank
100577,07/01/2015,phonebank
100933,07/01/2015,phonebank
100933,06/01/2015,phonebank
100933,06/01/2015,phonebank
100933,06/01/2015,phonebank
100933,06/01/2015,phonebank
100933,06/01/2015,phonebank
100933,06/01/2015,phonebank
100510,05/01/2015,phonebank

Open in new window

PortletPaulEE Topic AdvisorCommented:
2 more questions:

a. what is the table's name?
b. how do you get BOTH a row and a column from a single date field?

this I can sort of understand
userid      calldate        5/1/2015
100510      5/1/2015     5

but how do you also get data in an additional columns for that row?

userid      calldate      5/1/2015      6/1/2015      7/1/2015
100510      5/1/2015           5           2                     6
fb1990Author Commented:
I am using MS SQL Server Management Studio 2014. For brevity sake, I can call the table cust_order.

Thanks in advance for the assistance.  The calldate field is actually the first time the customers shows up in my database.  I want to include that because I need to know when the customer showed up and how many times they showed in the succeeding month.  The data will then be group by that date.

I hope i am making sense here,

Thanks again...
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

fb1990Author Commented:
To answer the question of how i did it.  Very manual indeed.  I had to do it in excel with excel formula.  It will be hard with several rows of data.  Of course, I used the code snippet that you helped with earlier on group the data into months.  The data was actually daily data, but summed and rolled up by monthdate...

Thanks.
PortletPaulEE Topic AdvisorCommented:
This should get you started at least. For an output like this:
| userid |               calldate | May | Jun | Jul | Aug |
|--------|------------------------|-----|-----|-----|-----|
| 100436 |  May, 01 2015 00:00:00 |   5 |   2 |   6 |   0 |
| 100510 |  May, 01 2015 00:00:00 |   2 |   3 |   1 |   0 |
| 100577 | June, 01 2015 00:00:00 |   0 |   4 |  11 |   0 |
| 100933 | June, 01 2015 00:00:00 |   0 |  12 |  24 |   0 |
| 101397 | July, 01 2015 00:00:00 |   0 |   0 |   1 |   0 |

Open in new window

The query is:
select
      userid
    , MIN(calldate) as calldate
    , SUM(case when month(calldate) = 5 then 1 else 0 end) as May
    , SUM(case when month(calldate) = 6 then 1 else 0 end) as Jun
    , SUM(case when month(calldate) = 7 then 1 else 0 end) as Jul
    , SUM(case when month(calldate) = 8 then 1 else 0 end) as Aug
from table1
group by userid
;

Open in new window


see http://sqlfiddle.com/#!9/95dbc/3  (using MySQL)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fb1990Author Commented:
Thank you Sir.  I will take it.  It does what I want.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.