Sql-server – Create pivot table from SQL query results

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

The following will work with some assumptions:

1. statusreciept will be 0 or 1- if other values are possible and you want EACH prior location concatenated in the "Prior location" field, that is also doable, but requires a more robust solution.

2. that createddatetime is unique for all sets you want to track

        ;WITH    ITR
                     AS ( SELECT   CREATEDDATETIME ,
                                   CREATEDBY ,
                                   INVENTID ,
                                   ITO.REFERENCECATEGORY ,
                                   IT.STATUSRECEIPT
                          FROM     dbo.TRANS IT
                                   INNER JOIN TRANSORIGIN ITO ON IT.TRANSORIGIN = ITO.RECID
                                                                       AND ITO.REFCATEGORY IN (
                                                                       2, 6 )
                          WHERE    CREATEDDATETIME > DATEADD(MONTH, -1, GETDATE())
                        ),
                 DS AS
       (
               SELECT  ITR.CREATEDDATETIME ,
                       ITR.CREATEDBY AS [User] ,
                       YPT.PALLETNUMBER AS PalletNumber ,
                       ID.LOCATIONID AS [Current Location] ,
                       ID.PALLETID AS Pallet ,
                       WOT.DEPOTNAME AS Depot ,
                       ITR.REFERENCECATEGORY ,
                       ITR.STATUSRECEIPT
   
               FROM    dbo.PALLETTABLE AS YPT
                       INNER JOIN dbo.PALLET AS WP ON YPT.PALLETID = WP.PALLETID
                       LEFT OUTER JOIN dbo.DIM AS ID ON WP.PALLETID = ID.PALLETID
                                                              AND ID.BATCHID <> ''
                       LEFT OUTER JOIN dbo.ORDER AS WOT ON ID.DIMID = WOT.DIMID
                       LEFT OUTER JOIN ITR ON ID.DIMID = ITR.DIMID
                       ORDER BY WP.PALLETNUMBER),
      msr AS
   (   SELECT createddatetime, max(statusreciept) statusreciept
   FROM ds
   GROUP BY createddatetime)
   
   
   
   SELECT ds.createddatetime,
          ds.user,
          ds.palletnumber,
          ds.[current location],
          ds2.[current location] as [Previous Location],
          ds.pallet,
          ds.depot,
          ds.reference_category,
          ds.statusreciept AS statusreciept
   FROM ds
   LEFT JOIN ds ds2 
      ON ds2.createddatetime = ds.createddatetime
      AND ds2.statusreciept = ds.statusreciept - 1
   INNER JOIN msr 
      ON msr.createddatetime = ds.createddatetime
      AND msr.statusreciept = ds.statusreciept
   GROUP BY ds.createddatetime,
          ds.user,
          ds.palletnumber,
          ds.[current location],
          ds2.[current location],
          ds.pallet,
          ds.depot,
          ds.reference_category
   

Please let me know if either of the assumptions are incorrect or you need a different implementation.