I am building a pivot query inside a CTE. I have a table `Table_1`:

```
Store      Week     xCount
-------    ----     ------
101        1        138
105        1        37
109        1        59
101        2        282
109        2        97
105        3        60
109        3        87

```

This is the query I used to pivot `Table_1`:

```
with CTE as 
(
    select 
        *
    from 
        (select 
             store, week, xCount
         from 
             table_1) src
    pivot
        (sum(xcount)
             for week in ([1], [2], [3])
    ) piv;
)
Select * 
From CTE

```

And this is the result I got:

```
| STORE |   1 |   2 |   3 |
+-------+-----+-----+-----+
|   101 | 138 | 282 | null|
|   105 |  37 | null|  60 |
|   109 |  59 |  97 |  87 |

```

The result is fine, but now there is one more WEEK added. 

I want to develop a CTE with pivot query that will automatically generate distinct weeks and create a column on that basis.

I did some research and found a recursive CTE can be used to do this. I am new to recursive CTE, so please anyone can help me to solve this issue.

I also tried dynamic pivot query but CTE does not allow dynamic query.

Please help.