Columns to Rows with varying dates

Please see the sample data and required output format below:

--SAMPLE TABLE
DECLARE @TEMP TABLE(
    DATA_DATE DATE,
    PROD_ID INT,
    CAT_CODE NVARCHAR(10),
    DATABUCKET_1 FLOAT,
    DATABUCKET_2 FLOAT,
    DATABUCKET_3 FLOAT,
    DATABUCKET_4 FLOAT,
    DATABUCKET_5 FLOAT);

INSERT INTO @TEMP VALUES('19-Oct-2018',100,'C1', 100,200,300,400,500)

SELECT * FROM @TEMP;

--PREFERRED OUTPUT FORMAT

SELECT 'C1' AS CAT_CODE, '19-Oct-2018' AS DATA_DATE, 100 AS UNITS, 'W1' AS WEEK_NUM--FOR DATABUCKET_1, THE DATE REMAINS SAME (AS DATA_DATE)
UNION ALL
SELECT 'C1' AS CAT_CODE, '12-Oct-2018' AS DATA_DATE, 200 AS UNITS, 'W2' AS WEEK_NUM--FOR DATABUCKET_2, THE DATE IS ONE WEEK BEFORE THAT OF W1
UNION ALL
SELECT 'C1' AS CAT_CODE, '05-Oct-2018' AS DATA_DATE, 300 AS UNITS, 'W3' AS WEEK_NUM--FOR DATABUCKET_3, THE DATE IS ONE WEEK BEFORE THAT OF W2
UNION ALL
SELECT 'C1' AS CAT_CODE, '28-Sep-2018' AS DATA_DATE, 400 AS UNITS, 'W4' AS WEEK_NUM--FOR DATABUCKET_4, THE DATE IS ONE WEEK BEFORE THAT OF W3
UNION ALL
SELECT 'C1' AS CAT_CODE, '21-Sep-2018' AS DATA_DATE, 500 AS UNITS, 'W5' AS WEEK_NUM--FOR DATABUCKET_5, THE DATE IS ONE WEEK BEFORE THAT OF W4

Few additional points:

My actual table has 106 data buckets and several other columns.
I have only given few here for the sake of simplicity.
Each month a new file is received with a different DATA_DATE value.
DATA_DATE value is same across one file and it corresponds to DATABUCKET_1
For other DATABUCKETS, the value is one week before.

Please let me know how I can achieve this using UNPIVOT. Thanks in advance

Solution

This can be done pretty easy using dynamic T-SQL statement. The idea is to get the columns that we need to use for unpivoting in advance and to add add order ID for each column. This number will be use to calculated the last column and the date column.

Note, I have changed the @table variable to normal table in order to be able to read the columns dynamically from the sys.columns view. Of course, in your real example you are free to populate the table as you like and order the columns as you like, too.

--DROP TABLE IF EXISTS [dbo].[Temp];

CREATE TABLE [dbo].[Temp](
    DATA_DATE DATE,
    PROD_ID INT,
    CAT_CODE NVARCHAR(10),
    DATABUCKET_1 FLOAT,
    DATABUCKET_2 FLOAT,
    DATABUCKET_3 FLOAT,
    DATABUCKET_4 FLOAT,
    DATABUCKET_5 FLOAT);

INSERT INTO [dbo].[Temp] VALUES ('19-Oct-2018',100,'C1', 100,200,300,400,500)

SELECT * FROM [dbo].[Temp];

--PREFERRED OUTPUT FORMAT

SELECT 'C1' AS CAT_CODE, '19-Oct-2018' AS DATA_DATE, 100 AS UNITS, 'W1' AS WEEK_NUM--FOR DATABUCKET_1, THE DATE REMAINS SAME (AS DATA_DATE)
UNION ALL
SELECT 'C1' AS CAT_CODE, '12-Oct-2018' AS DATA_DATE, 200 AS UNITS, 'W2' AS WEEK_NUM--FOR DATABUCKET_2, THE DATE IS ONE WEEK BEFORE THAT OF W1
UNION ALL
SELECT 'C1' AS CAT_CODE, '05-Oct-2018' AS DATA_DATE, 300 AS UNITS, 'W3' AS WEEK_NUM--FOR DATABUCKET_3, THE DATE IS ONE WEEK BEFORE THAT OF W2
UNION ALL
SELECT 'C1' AS CAT_CODE, '28-Sep-2018' AS DATA_DATE, 400 AS UNITS, 'W4' AS WEEK_NUM--FOR DATABUCKET_4, THE DATE IS ONE WEEK BEFORE THAT OF W3
UNION ALL
SELECT 'C1' AS CAT_CODE, '21-Sep-2018' AS DATA_DATE, 500 AS UNITS, 'W5' AS WEEK_NUM--FOR DATABUCKET_5, THE DATE IS ONE WEEK BEFORE THAT OF W4


DECLARE @DynamicTSQLStatement NVARCHAR(MAX)
       ,@ColumnNames NVARCHAR(MAX);

--DROP TABLE IF EXISTS #Columns;

CREATE TABLE #Columns
(
    [ID] INT
   ,[name] SYSNAME
);

INSERT INTO #Columns ([ID], [name])
SELECT ROW_NUMBER() OVER (ORDER BY [column_id]) - 1
      ,[name]
FROM [sys].[columns]
WHERE [object_id] = OBJECT_ID('[dbo].[Temp]')
    AND [name] LIKE '%DATABUCKET%';

SELECT @ColumnNames = STUFF
(
    (
        SELECT ',[' + [name] + ']'
        FROM #Columns
        ORDER BY [id]
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
   ,1
   ,1
   ,''
);


SET @DynamicTSQLStatement = N'
SELECT [CAT_CODE]
      ,DATEADD(WEEK, -1 * C.id, DATA_DATE) AS DATA_DATE
      ,value as UNITS
      ,''W'' + CAST(c.id + 1 AS VARCHAR(8)) as [WEEK_NUM]
FROM [dbo].[Temp] 
UNPIVOT
(
    [value] FOR [column] IN ('+ @ColumnNames +')
) UNPVT
INNER JOIN #Columns C
    ON UNPVT.[column] = c.[name] 
ORDER BY DATA_DATE DESC
;'

EXEC sp_executesql @DynamicTSQLStatement;

So, this is the idea. It is up to to manipulate the code to work with your data.