SQL count - showing week range as column headers

Question

i have the following sql

    SELECT    COUNT(*) as Total, SentByText
FROM     letterCase WITH (nolock)
WHERE     (SenttoClientDate BETWEEN DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) AND DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6))
group by SentByText

this counts all records that match criterea and displays results like

Total       SentByText
    5          Fred
    4          Colin
    2          Bob
    1          Billy

my question is how would i get results to show like this - having the date range as column headers

SentByText    10/10/2016  11/10/2016  12/10/2016  13/10/2016  
 fred              4           5           2          2           
 colin             7           2           0          2                  
 bob               2           1          12          6                 
 billy             9           0           0          2

ive looked at using a pivot but not sure how i go about showing dates without hardcoding

score 1 · Answer 1 · edited May 23 '17 at 10:34

You need dynamic sql see SQL Server dynamic PIVOT query? for example. The only essential difference from other dynamic pivots is the way you create column list using tally table (sys.all_objects here):

declare @cols varchar(max) = 
         stuff(
             (select top(7)  ','+quotename(convert(varchar(12),DATEADD(wk,DATEDIFF(wk,0,GETDATE()), row_number() over(order by (select null)) -1) ,103))
                from sys.all_objects for xml path(''))
             ,1,1,'')

select @cols;

Don't forget to convert pivoted date the same way in dynamic query .. dt = convert(varchar(12),SenttoClientDate),103) ..

SQL count - showing week range as column headers

1 Answers1