PostgreSQL – How to Rotate a Table with Multiple Value Columns

pivotpostgresql

I'm not sure if this can be done with crosstab(). I've been using this questions/answer as a reference, but am having difficulty putting together the query.
My table looks like this, just that I have over 50 distinct values in the week column:

+------+----+--------+---------+--------+
| week | id | param1 | param2  | param3 |
+------+----+--------+---------+--------+
|    1 |  1 |     13 |      10 |     12 |
|    1 |  2 |     12 |      11 |     44 |
|    2 |  1 |     34 |      33 |      3 |
|    2 |  2 |      3 |      44 |      3 |
+------+----+--------+---------+--------+

I would like to rotate it such that it looks like this:

+----+----------+-----------+----------+----------+----------+----------+-----+
| id | w1param1 | w1param2  | w1param3 | w2param1 | w2param2 | w2param3 | ... |
+----+----------+-----------+----------+----------+----------+----------+-----+
|  1 |       13 |        10 |       12 |       34 |       33 |        3 | ... |
|  2 |       12 |        11 |       44 |        3 |       44 |        3 | ... |
+----+----------+-----------+----------+----------+----------+----------+-----+

where the week column is used to number the parameter columns, and the rows become columns.

Best Answer

The (2nd form of the) crosstab() function expects these columns as input:

1 row_name column
(0-n) extra columns
1 category column
1 value column

See:

Pivot on Multiple Columns using Tablefunc

Your specific difficulty is that you are trying to process 3 value columns at once (param1, param2, param3). Your input table is already "half pivoted". There are various ways to solve this. Joining three crosstab queries is probably cleanest. Demonstrating for 5 weeks:

SELECT *
FROM   crosstab(
     'SELECT id, week, param1
      FROM   tbl
      ORDER  BY 1,2'
   , 'SELECT generate_series(1,5)'
   ) ct1 (id int, w1p1 int, w1p2 int, w1p3 int, w1p4 int, w1p5 int)
JOIN   crosstab(
     'SELECT id, week, param2
      FROM   tbl
      ORDER  BY 1,2'
   , 'SELECT generate_series(1,5)'
   ) ct2 (id int, w2p1 int, w2p2 int, w2p3 int, w2p4 int, w2p5 int) USING (id)
JOIN   crosstab(
     'SELECT id, week, param3
      FROM   tbl
      ORDER  BY 1,2'
   , 'SELECT generate_series(1,5)'
   ) ct3 (id int, w3p1 int, w3p2 int, w3p3 int, w3p4 int, w3p5 int) USING (id)

dbfiddle here

[INNER] JOIN is safe, since all instances are guaranteed to return the same week ids. Else we'd use FULL JOIN.

With over 50 weeks, you get over 150 columns. Is that really what you want?

Related Solutions

SQL Server – Creating Multiple Table Pivot

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

SQL Query – How to Return Columns Within a Given Range

Your best bet is probably to use dynamic SQL to pivot your data. Here try this out.

IF OBJECT_ID('tempdb..#tblItems') IS NOT NULL
    DROP TABLE #tblItems;
IF OBJECT_ID('tempdb..##tblItemsTotal') IS NOT NULL
    DROP TABLE ##tblItemsTotal;

CREATE TABLE #tblItems
(
    UniqueCode CHAR(4),
    [Description] VARCHAR(100),
    [Date] Date,
    Amount Decimal(18,3)
);

INSERT INTO #tblItems
VALUES  ('ABCD','Item Description 1','01/03/2014',2068),
        ('PQRS','Item Description 2','01/05/2014',412.104),
        ('PQRS','Item Description 2','01/07/2014',635.448),
        ('UVWX','Item Description 3','01/05/2014',214.52),
        ('UVWX','Item Description 3','01/07/2014',827.2);

--Now that I've set up the problem, here's for the actual code

--Declare your date range and variables to hold column lists
DECLARE @startDate DATE = '01/03/2014',
        @endDate DATE = '01/07/2014',
        @SelectCols VARCHAR(MAX),
        @PivotCols VARCHAR(MAX);

--Caluclate column lists
SELECT  @SelectCols =  COALESCE(@SelectCols + ',','') + 'ISNULL(pvt.' + QUOTENAME(CAST([Date] AS VARCHAR(25))) + ',0) AS ' + QUOTENAME(CAST([Date] AS VARCHAR(25))),
        @PivotCols =  COALESCE(@PivotCols + ',','') + QUOTENAME(CAST([Date] AS VARCHAR(10)))
FROM    #tblItems
WHERE   [Date] BETWEEN @startDate AND @endDate
GROUP BY [Date];

--Calculate your total within data range
SELECT  UniqueCode,
        [Description],
        SUM(amount) Total INTO ##tblItemsTotal
FROM #tblItems
WHERE [Date] BETWEEN @startDate AND @endDate
GROUP BY UniqueCode,[Description];

--Use dynamic SQL to pivot your data and join to ##tblItemsTotal for the Total column
EXEC
(
    'SELECT pvt.UniqueCode,
            pvt.[Description],' +
            @selectCols +
            ',B.Total
     FROM #tblItems A
     PIVOT
     (
        SUM(Amount) FOR [Date] IN (' + @PivotCols + ')
     ) pvt
     INNER JOIN ##tblItemsTotal B 
     ON pvt.UniqueCode = B.UniqueCode'
)

Best Answer

Related Solutions

SQL Server – Creating Multiple Table Pivot

SQL Query – How to Return Columns Within a Given Range

Related Question