CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012pivot
Ali
Ali

Reputation: 684

categorize data by months of year

I have a table which contains of information about the customers and agents. I want to categorize them by month of year .

to clarify: I would like to know how many customers which registered under different agents in month of a year , same as the below figure :

enter image description here

Upvotes: 1

Views: 285

Answers (2)

Morteza Bashardoost
Morteza Bashardoost

Reputation: 78

SELECT * FROM (

SELECT year(DATE) as [year],left(datename(month,DATE),3)as [month], AGENT_CODE as cnt FROM yourTable

) as s PIVOT ( count(AGENT_CODE) FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec) )AS pivot

Upvotes: 1

MarcinJuraszek
MarcinJuraszek

Reputation: 125610

You can use PIVOT:

SELECT AGENT_CODE, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]
FROM 
(SELECT CUSTOMER_ID, AGENT_CODE, MONTH(DATE) as m
FROM Test) t
PIVOT
(
COUNT (CUSTOMER_ID)
FOR m IN
( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )
) AS pvt;

returns

AGENT_CODE  1   2   3   4   5   6   7   8   9   10  11  12
100         1   0   0   0   2   1   1   0   1   0   0   3
101         0   0   2   0   0   0   1   0   0   1   1   0
102         0   0   0   0   0   0   1   1   0   0   0   0
103         0   0   0   1   0   0   0   0   0   0   0   0

add column aliases to get JAN, FEB, etc. instead of numbers as columns titles.

Working demo: http://sqlfiddle.com/#!6/673cb/7

Upvotes: 4

Related Questions