SQL Server Generate Dynamic columns using Pivot

Introduction

SQL Server PIVOT operator rotates a table-valued expression. It turns the unique values in one column into multiple columns in the output and performs aggregations on any remaining column values.
You follow these steps to make a query a pivot table:

• First, select a base dataset for pivoting. 
• Second, create a temporary result by using a derived table or common table expression (CTE) 
• Third, apply the PIVOT operator.

If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns.
It sounds like you will need to use dynamic SQL if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.

For Example :

First up, here are some quick table definitions and data for use:

CREATE TABLE #Product
(
  [S] int,
  [W] int,
  [C] int
);

INSERT INTO #Product
(
  [S],
  [W], [C]
)
VALUES
    (102, 1, 96),
    (101, 1, 138),
    (105, 1, 37),
    (109, 1, 59),
    (101, 2, 282),
    (102, 2, 212),
    (105, 2, 78),
    (109, 2, 97),
    (105, 3, 60),
    (102, 3, 123),
    (101, 3, 220),
    (109, 3, 87);

If your values are known, then you will hard-code the query:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Week)
                    from #Product
                    group by Week
                    order by Week
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT S,' + @cols + ' from
             (
                select S, W, C
                from #Product
            ) x
            pivot
            (
                sum(xCount)
                for week in (' + @cols + ')
            ) p '
execute(@query);

The dynamic version generates the list of week numbers that should be converted to columns. Both give the same result:

|    S    |   1    |   2  |   3   |
---------------------------
|   101 | 138 | 282 | 220 |
|   102 |  96  | 212 | 123 |
|   105 |  37  |  78  |  60  |

|   109 |  59  |  97  |  87  |