Sql-server – Single year column and multiple weeks columns into single year/week column with PIVOT

sql servert-sqlunpivot

I have a table that looks roughly like this.

(Pk int, Year int, Week1, Week2, .., Week53)

The values in each "Week" column are sales data. What I want to do is create a new table from the first table in the form of –

(Pk int, WeekYear, SalesData)

Where "WeekYear" is a concatentation of the year and the week number and SalesData is the same salesdata for that respective field in the former table. (just trying to get the select down for now)

SELECT pk,
   [1],
   [2],
   ...
   [1000]
 FROM (SELECT * FROM SalesTable) st
PIVOT
(
AGGREGATE(pk) -- no idea what to use here
FOR pk IN [1],
   [2],
   ...
   [1000]
 );

My current attempts make no sense as I'm not sure which aggregate function to apply on my dataset. I'm also having a bit of trouble discerning the best way to create the new column YearWeek.

This seemed like an obvious use for PIVOT (I need to transpose the table). However, I'm having a lot of trouble wrapping my head around how to properly accomplish it. What would be the best way to go about doing this?

Best Answer

This sounds more like an UNPIVOT to me.

SELECT SalesTable.Pk, CrossApplied.WeekYear, CrossApplied.Value
FROM SalesTable
CROSS APPLY (VALUES ('01'+CAST([Year] AS CHAR(40)), Week1),
                    ('02'+CAST([Year] AS CHAR(40)), Week2),
                    ('03'+CAST([Year] AS CHAR(40)), Week3),
                    .......)
        CrossApplied (WeekYear, Value)

There are other ways to do an UNPIVOT but using CROSS APPLY is my favorite. I give a more general example here.

Related Solutions

Sql-server – Need to divide single column into multiple columns

For the first case you can use ROW_NUMBER then PIVOT to do this using the modulo operator to divide into columns and the result of integer divsion by 5 to group into rows.

WITH CTE AS
(
SELECT C,
       (ROW_NUMBER() OVER (ORDER BY C) -1)%5 AS Col,
       (ROW_NUMBER() OVER (ORDER BY C) -1)/5 AS Row
FROM YourTable       
)
SELECT [0], [1], [2], [3], [4]
FROM CTE
PIVOT (MAX(C) FOR Col IN ([0], [1], [2], [3], [4])) AS Pvt
ORDER BY Row

For the second case Oracle has a straight forward syntax to achieve this with PIVOT. SQL Server doesn't but you can do this as below.

WITH CTE AS
(
SELECT *,
       (ROW_NUMBER() OVER (ORDER BY Column1) -1)%5 AS Col,
       (ROW_NUMBER() OVER (ORDER BY Column1) -1)/5 AS Row
FROM YourTable       
)
SELECT MAX(CASE WHEN Col = 0 THEN Column1 END),
       MAX(CASE WHEN Col = 0 THEN Column2 END),
       MAX(CASE WHEN Col = 1 THEN Column1 END),
       MAX(CASE WHEN Col = 1 THEN Column2 END),
       MAX(CASE WHEN Col = 2 THEN Column1 END),
       MAX(CASE WHEN Col = 2 THEN Column2 END),
       MAX(CASE WHEN Col = 3 THEN Column1 END),
       MAX(CASE WHEN Col = 3 THEN Column2 END),
       MAX(CASE WHEN Col = 4 THEN Column1 END),
       MAX(CASE WHEN Col = 4 THEN Column2 END)
FROM CTE
GROUP BY Row
ORDER BY Row

Sql-server – Single row to multiple rows with column combination

Given this test data:

DECLARE @T AS TABLE
(
    ZIP sql_variant NOT NULL,
    STATE sql_variant NOT NULL,
    F sql_variant NULL,
    C sql_variant NULL,
    F1 sql_variant NULL,
    C1 sql_variant NULL,
    F2 sql_variant NULL,
    C2 sql_variant NULL,
    F3 sql_variant NULL,
    C3 sql_variant NULL
);

INSERT @T 
    (ZIP, STATE, F, C, F1, C1, F2, C2)
VALUES 
    ('A', 'AS', 'AF', 'AC', 'AF1', 'AC1', 'AF2', 'AC2');

INSERT @T 
    (ZIP, STATE, F, C, F1, C1)
VALUES 
    ('B', 'BS', 'BF', 'BC', 'BF1', 'BC1');

One way to produce the desired output is:

SELECT
    T.ZIP,
    T.STATE,
    CA.F,
    CA.C
FROM @T AS T
CROSS APPLY
(
    VALUES
        (F, C),
        (F1, C1),
        (F2, C2),
        (F3, C3)
) AS CA (F, C)
WHERE
    CA.F IS NOT NULL
    OR CA.C IS NOT NULL;

Related Question