Sql Server Aggregation or Pivot Table Query -

i'm trying write query tell me number of customers had number of transactions each week. don't know start query, i'd assume involves aggregate or pivot function. i'm working in sqlserver management studio.

currently data looks first column customer id , each subsequent column week :

|customer| 1 | 2| 3 |4 | ---------------------- |001     |1 | 0| 2 |2 | |002     |0 | 2| 1 |0 | |003     |0 | 4| 1 |1 | |004     |1 | 0| 0 |1 | 

i'd see return following:

|visits |1 | 2| 3 |4 | ---------------------- |0      |2 | 2| 1 |0 | |1      |2 | 0| 2 |2 | |2      |0 | 1| 1 |1 | |4      |0 | 1| 0 |0 | 

what want count of customer transactions per week. e.g. during 1st week 2 customers (i.e. 002 , 003) had 0 transactions, 2 customers (i.e. 001 , 004) had 1 transaction, whereas 0 customers had more 1 transaction

the query below result want, note has column names hard coded. it's easy add more week columns, if number of columns unknown might want solution using dynamic sql (which require accessing information schema column names). it's not hard turn dynamic version though.

select      visits     , coalesce([1],0) week1      , coalesce([2],0) week2      , coalesce([3],0) week3      , coalesce([4],0) week4 (     select *, count(*) c (     select '1' w, week1 visits t union     select '2' w, week2 visits t union     select '3' w, week3 visits t union     select '4' w, week4 visits t ) group w, visits ) x pivot ( max (c) w in ([1], [2], [3], [4]) ) pvt; 

in query table called t , output is:

visits  week1   week2   week3   week4 0       2       2       1       1 1       2       0       2       2 2       0       1       1       1 4       0       1       0       0