SQL for Multiple rows into one row with different column counts grouped by Category and date

I have a small sample of one of our tables:

User   Category  InitialDate   LastRequestDate   LastUpdate
1      1907      1/1/2010      2/1/2011          1/15/2011
2      6509      6/10/2010     4/1/2011          3/15/2011
3      3102      3/1/2010      3/15/2011         2/17/2011
4      3102      3/1/2010      2/15/2011         3/17/2011
5      6509      5/10/2010     2/1/2011          1/25/2011
6      1907      1/1/2010      3/1/2011          4/15/2011

Each user has one row in the table and users are constantly being added.

There are currently about 30 categories but new categories are always being added.

I need to do a summary table for each day for each category what are the counts...

Do you have a suggestion for the best way to handle this besides doing temporary tables, cursors, etc...? I'm thinking something with row_number partition or / and cte...but not sure how to do this... Group By won't work because of the dates being on same row... When use Group by I get a 3 rows for each SKU with duplicate counts...

Date           Catalog    InitialCount    LastRequestCount     LastUpdateCount
1/1/2010       1907       2               0                    0       
3/1/2010       3102       2               0                    0
5/10/2010      6509       1               0                    0
6/10/2010      6509       1               0                    0
1/15/2011      1907       0               0                    2
1/25/2011      6509       0         开发者_如何学运维      0                    1
2/1/2011       1907       0               1                    0
2/1/2011       6509       0               1                    0
2/15/201       3102       0               1                    0
2/17/2011      3102       0               0                    1
3/1/2011       1907       0               1                    0
3/15/2011      3102       0               1                    0
3/17/2011      3102       0               0                    1
4/1/2011       6509       0               1                    0
4/15/2011      1907       0               0                    1

Run a pre-query of distinct instances of each category and respective dates... Then, use that as basis to get next level...

    select 
          PreQuery.UniqDate,
          PreQuery.Category,
          sum( case when PreQuery.UniqDate = YT.InitialDate then 1 else 0 end ) InitialCount,
          sum( case when PreQuery.UniqDate = YT.LastRequestDate then 1 else 0 end ) LastRequestCount,
          sum( case when PreQuery.UniqDate = YT.LastUpdate then 1 else 0 end ) LastUpdateCount
       from
          ( select distinct
                  Category,
                  InitialDate UniqDate
               from 
                  YourTable
            union
            select
                  Category,
                  LastRequestDate UniqDate
               from
                  YourTable
            union
            select
                  Category,
                  LastUpdate UniqDate ) PreQuery
          join YourTable YT
             on PreQuery.Category = YT.Category
             AND ( PreQuery.UniqDate = YT.InitialDate
                 OR PreQuery.UniqDate = YT.LastRequestDate
                 OR PreQuery.UniqDate = YT.LastUpdate )
      group by
         PreQuery.UniqDate,
         PreQuery.Category

Can you just take the query you have shown the results for, call it T, and do

SELECT Date, Catalog, SUM(InitialCount) AS InitialCount,
             SUM(LastRequestCount) AS LastRequestCount,
             SUM(LastUpdateCount) AS LastUpdateCount
FROM ( /* your existing query goes here */ ) AS T GROUP BY Date, Catalog;

Since the two of the three entries are zeroes, does this give what you want?