--If your values are known, then you will hard-code the query:
select *
from
(
select store, week, xCount
from yt
) src
pivot
(
sum(xcount)
for week in ([1], [2], [3]) --[1],[2],[3] are the
week numbers hardcoded
) piv;
select *
from
(
select YEAR(CONVERT(NVARCHAR,[dDateTimeCompleted],101)) AS [CCDA], MONTH(CONVERT(NVARCHAR,[dDateTimeCompleted],101)) AS [week]
from [Scripts].[dbo].[WorkTable]
where YEAR(CONVERT(NVARCHAR,[dDateTimeCompleted],101)) is not null
) src
pivot
(
COUNT(week)
for week in ([1], [2], [3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) piv;
--Results
/*
CCDA 1 2 3 4 5 6 7 8 9 10 11 12
2015 0 0 0 0 0 0 19 3 31 24 14 18
2016 11 6 23 8 20 21 29 19 31 15 29 23
2017 24 13 7 18 31 8 7 11 19 0 0 0
*/
-- https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server
With pivot_data as
(
select
action, -- grouping column
view_edit -- spreading column
from tbl
)
select action, [view], [edit]
from pivot_data
pivot ( max(view_edit) for view_edit in ([view], [edit]) ) as p;
--https://stackoverflow.com/questions/24470/sql-server-examples-of-pivoting-string-data