asked on

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

PortletPaul

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

PortletPaul

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

fb1990

ASKER

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...

fb1990

ASKER

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.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fb1990

ASKER

Thank you Sir. I will take it. It does what I want.