Pivot – How to Pivot Multiple Columns in SQL

I don't have data on what links your tables so you might have to do a very little change to the ON clauses, but here it is:

Creating tables with data

CREATE TABLE #sales (PK int, Year int, Wk1 int, Wk2 int, Wk3 int, Wk4 int);
GO
INSERT INTO #sales VALUES (1,2012,9,5,3,4);
INSERT INTO #sales VALUES (2,2013,7,2,6,3);
INSERT INTO #sales VALUES (3,2014,6,5,6,1);
GO
CREATE TABLE #quantity (PK int, Year int, Wk1 int, Wk2 int, Wk3 int, Wk4 int);
GO
INSERT INTO #quantity VALUES (1,2012,14,54,13,49);
INSERT INTO #quantity VALUES (2,2013,8,12,17,31);
INSERT INTO #quantity VALUES (3,2014,16,25,25,3);
GO
CREATE TABLE #orders (PK int, Year int, Wk1 int, Wk2 int, Wk3 int, Wk4 int);
GO
INSERT INTO #orders VALUES (1,2012,2,5,3,9);
INSERT INTO #orders VALUES (2,2013,1,2,7,1);
INSERT INTO #orders VALUES (3,2014,1,5,5,2);
GO

And the query:

SELECT PK, CAST(Year AS CHAR(4)) + '_' + Year_Week, Sales, Quantity, Orders
FROM
    (SELECT s.PK, s.Year
        , s.Wk1, s.Wk2, s.Wk3, s.Wk4
        , q.Wk1 AS qWk1, q.Wk2 AS qWk2, q.Wk3 AS qWk3, q.Wk4 AS qWk4
        , o.Wk1 AS oWk1, o.Wk2 AS oWk2, o.Wk3 AS oWk3, o.Wk4 AS oWk4
    FROM #sales s
    INNER JOIN #quantity q ON s.PK = q.PK AND s.YEAR = q.YEAR
    INNER JOIN #orders o ON s.PK = o.PK AND s.YEAR = o.YEAR) p
UNPIVOT
    (Sales FOR Year_Week IN
        (Wk1, Wk2, Wk3, Wk4)
    )AS unpvt_s
UNPIVOT
    (Quantity FOR Year_Week_q IN
        (qWk1, qWk2, qWk3, qWk4)
    )AS unpvt_q
UNPIVOT
    (Orders FOR Year_Week_o IN
        (oWk1, oWk2, oWk3, oWk4)
    )AS unpvt_o
WHERE 'q'+Year_Week = Year_Week_q
    AND 'o'+Year_Week = Year_Week_o;
GO

So here's what I did. I put what you had in a temp table and then grouped the results like so:

;WITH data AS (
  SELECT grp, k, k_ts = k + '_ts', ts, val
  FROM @Source

)
SELECT *
INTO #tmp
FROM data
PIVOT ( MAX(val) FOR k IN ([color], [shape], [subst]) ) p1
PIVOT ( MAX(ts) FOR k_ts IN ([color_ts], [shape_ts], [subst_ts]) ) p2

Select 
    grp
    , max(color) as color
    , max(shape) as shape
    , max(subst) as subst
    , max(color_ts) as color_ts
    , max(shape_ts) as shape_ts
    , max(subst_ts) as subst_ts
from #tmp
group by grp

It's not pretty, but it worked.