CREATE PROCEDURE sp_CreateWeeklyRowBasedStockDataTable
AS
BEGIN
    /*********************************************/
    /* Step 1.                                   */
    /* Get names for columns in select statement */
    /*********************************************/
    SET DATEFIRST 1

    DECLARE @columnNames NVARCHAR(MAX) =
        (SELECT (
            SELECT DISTINCT TOP 1023 '['
            + CAST(DATEPART(yy,company_stocks_date) AS VARCHAR(MAX)) + '-' + RIGHT(Replicate('0', 2) + CAST( DATEPART(wk, company_stocks_date) AS NVARCHAR), 2)
            + '], '
            FROM company_stocks
            WHERE   DATEPART(DW, company_stocks_date) = 1
            ORDER BY 1 DESC
            FOR XML PATH('')
            ) AS names)

    SET @columnNames = SUBSTRING(@columnNames, 1, (len(@columnNames) -1))

    /*******************************************/
    /* Step 2.                                 */
    /* Get names for creating a table with one */
    /* column for each record                  */
    /*******************************************/
    DECLARE @tableColumnNames NVARCHAR(MAX) =
        (SELECT (
            SELECT DISTINCT TOP 1023 '['
                + CAST(DATEPART(yy,company_stocks_date) AS VARCHAR(MAX)) + '-' + RIGHT(Replicate('0', 2) + CAST( DATEPART(wk, company_stocks_date) AS NVARCHAR), 2)
                + '] smallmoney NULL, '
            FROM company_stocks
            WHERE   DATEPART(DW, company_stocks_date) = 1
            ORDER BY 1 DESC
            FOR XML PATH('')
            ) AS names)

    /******************/
    /* Step 3.        */
    /* Generate table */
    /******************/
    DECLARE @query NVARCHAR(MAX) =
    'CREATE TABLE rowBasedStockDataWeekly (
        company_stocks_symbol CHAR(7) NOT NULL,'
    + @tableColumnNames
    + ' PRIMARY KEY (company_stocks_symbol))'

    DROP TABLE IF EXISTS rowBasedStockDataWeekly
    EXEC sp_executesql @query

    /***************/
    /* Step 4.     */
    /* Fill table  */
    /***************/
    SET @query='
    DECLARE @temp_company_stocks TABLE
        (
        company_stocks_symbol char(7),
        company_stocks_date char(7),
        company_stocks_adjusted_price_close smallmoney
        )

    INSERT INTO @temp_company_stocks
    SELECT  company_stocks_symbol,
            ( CAST(DATEPART(yy,company_stocks_date) as VARCHAR(MAX)) + ''-'' + RIGHT(Replicate(''0'', 2) + CAST( DATEPART(wk, company_stocks_date) AS NVARCHAR), 2) ) as company_stocks_date,
            avg(company_stocks_adjusted_price_close)
    FROM    company_stocks
    GROUP BY company_stocks_symbol, ( CAST(DATEPART(yy,company_stocks_date) as VARCHAR(MAX)) + ''-'' + RIGHT(Replicate(''0'', 2) + CAST( DATEPART(wk, company_stocks_date) AS NVARCHAR), 2) )

    INSERT INTO rowBasedStockDataWeekly
    SELECT  company_stocks_symbol,
            '
                + @columnNames +
            '
    FROM    @temp_company_stocks
    PIVOT(
        avg(company_stocks_adjusted_price_close)
        for company_stocks_date
        IN ( ' + @columnNames + ')
    )
    AS Tests'

    EXEC sp_executesql @query
END
GO