Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
imranabdulaziz
Yak Posting Veteran
83 Posts |
 Posted - 2008-04-07 : 07:23:14
|
| Hi all,i am using sql server 2005.let me explain the scenario.I have to built a report where I have to find out no of call made by an executive to a perticular customer during a year my table record look like empid custid product date and other fieldem111 iimran aaaa 01-04-2008 ........em111 iimran bbbb 01-04-2008 ........em111 iimran cccc 01-04-2008 ........em111 xxxxxx aaaa 01-04-2008 ........em111 xxxxxx 2222 01-04-2008 ........em111 iimran aaaa 02-04-2008 ........em111 iimran bbbb 02-04-2008 ........em111 iimran cccc 02-04-2008 ........em111 xxxxxx aaaa 02-04-2008 ........em111 xxxxxx 2222 02-04-2008 .........problem is it has to implement in this mannerSrno custname jan feb mar apr may june july aug sep oct nov dec1 iimran 0 4 0 5 6 7 0 3 5 7 3 1please suggest some idea.Please Help |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-07 : 07:39:26
|
1. Search for PIVOT / CROSSTAB queries.2. Use new PIVOT operator3. Use old school syntaxSELECT custid, sum(case when datepart(month, productdate) = 1 then 1 else 0 end) AS January,sum(case when datepart(month, productdate) = 2 then 1 else 0 end) AS February,...,FROM TableGROUP BY custid
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
|
|
|
formating the record