Teradata: Transpose Rows To Columns

Pradeep

Teradata: Transpose Rows to Column

Sometime it is required to transpose rows into columns. This may be required for various business requirement and better analysis. Lets try to achieve this with the help of a table named 'Ledger' which contains company's sales for each quarter and company's sales for each quarter need to converted to columns.

Ledger Table: Creating a sample volatile table ledger with 3 columns to be transposed as shown below.


CREATE  VOLATILE TABLE ledger
( 
  year_nr          INTEGER,
  Quarter          VARCHAR(10),
  Sales            DECIMAL(18,0)
)
ON COMMIT PRESERVE ROWS;

Data Population: Inserting few records in the table for transposing them to columns


INSERT INTO ledger VALUES(2015,'Q1',90);
INSERT INTO ledger VALUES(2015,'Q2',70);
INSERT INTO ledger VALUES(2015,'Q3',130);
INSERT INTO ledger VALUES(2015,'Q4',30);
INSERT INTO ledger VALUES(2016,'Q1',40);
INSERT INTO ledger VALUES(2016,'Q2',50);
INSERT INTO ledger VALUES(2016,'Q3',120);
INSERT INTO ledger VALUES(2016,'Q4',20);

Data View: Checking data in the 'ledger' table


SELECT * FROM ledger;

Output:
year  Quarter   Sales
2015  Q1        90
2015  Q2        70
2015  Q3        130
2015  Q4        30
2016  Q1        40
2016  Q2        50
2016  Q3        120
2016  Q4        20

Solution 1: Below query can transpose row to column for the given table 'ledger' using case statement.


Select year_nr,
    SUM(CASE WHEN Quarter='Q1' THEN Sales END) Q1,
    SUM(CASE WHEN Quarter='Q2' THEN Sales END) Q2,
    SUM(CASE WHEN Quarter='Q3' THEN Sales END) Q3,
    SUM(CASE WHEN Quarter='Q4' THEN Sales END) Q4
FROM Ledger
GROUP BY year_nr;

Output:
year_nr  Q1     Q2       Q3      Q4
-------  -----  -------  ------  ------
2015     90     70       130     30
2016     40     50       120     20

Solution 2: Same result can be achieved using Teradata PIVOT function (introduced in TD 16).


SELECT *
FROM ledger PIVOT (
SUM(sales)
FOR Quarter IN ('Q1' AS Q1,
                'Q2' AS Q2,
                'Q3' AS Q3,
                'Q4' AS Q4)
)Temp_pivot;

Output:
year_nr  Q1     Q2       Q3      Q4
-------  -----  -------  ------  ------
2015     90     70       130     30
2016     40     50       120     20

Using PIVOT function with more groups of columns

Creating a table and populating sample data


CREATE  VOLATILE TABLE ledger_multigroup
( 
  year_nr          INTEGER,
  Quarter          VARCHAR(10),
  Sales            DECIMAL(18,0),
  profit           DECIMAL(18,0)
)
ON COMMIT PRESERVE ROWS;

--Data
INSERT INTO ledger_multigroup VALUES(2015,'Q1',90,3);
INSERT INTO ledger_multigroup VALUES(2015,'Q2',70,6);
INSERT INTO ledger_multigroup VALUES(2015,'Q3',130,2);
INSERT INTO ledger_multigroup VALUES(2015,'Q4',30,6);
INSERT INTO ledger_multigroup VALUES(2016,'Q1',40,1);
INSERT INTO ledger_multigroup VALUES(2016,'Q2',50,8);
INSERT INTO ledger_multigroup VALUES(2016,'Q3',120,9);
INSERT INTO ledger_multigroup VALUES(2016,'Q4',20,7);

Solution 1: It will be easier to use Teradata PIVOT (introduced in TD 16) function for more groups of columns.


SELECT *
FROM ledger_multigroup PIVOT (
SUM(sales) AS sales, SUM(profit) as profit
FOR Quarter IN ('Q1' AS Q1,
                'Q2' AS Q2,
                'Q3' AS Q3,
                'Q4' AS Q4)
)Temp_pivot;

Output:
year_nr  Q1_sales  Q1_profit  Q2_sales  Q2_profit  Q3_sales  Q3_profit  Q4_sales  Q4_profit
-------  --------  ---------  --------  ---------  --------  ---------  --------  ---------
   2015       90         3       70         6      130         2       30         6
   2016       40         1       50         8      120         9       20         7

Solution 2: Same result can be achieved using case statement


Select year_nr,
    SUM(CASE WHEN Quarter='Q1' THEN Sales END) Q1_Sales,
    SUM(CASE WHEN Quarter='Q2' THEN Sales END) Q2_Sales,
    SUM(CASE WHEN Quarter='Q3' THEN Sales END) Q3_Sales,
    SUM(CASE WHEN Quarter='Q4' THEN Sales END) Q4_Sales,
    SUM(CASE WHEN Quarter='Q1' THEN profit END) Q1_profit,
    SUM(CASE WHEN Quarter='Q2' THEN profit END) Q2_profit,
    SUM(CASE WHEN Quarter='Q3' THEN profit END) Q3_profit,
    SUM(CASE WHEN Quarter='Q4' THEN profit END) Q4_profit
FROM Ledger
GROUP BY year_nr;

Output:
year_nr   Q1_Sales   Q2_Sales    Q3_Sales   Q4_Sales   Q1_profit   Q2_profit  Q3_profit   Q4_profit
--------  ---------  ---------  ----------  ---------  ----------  ---------- ----------  ----------
    2015        90        70        130        30          3          6         2          6
    2016        40        50        120        20          1          8         9          7

Sometime it is required to transpose rows into columns for better visualization. This may be required for various business requirement and better analysis.

dbmstutorials.com

Teradata: Transpose Rows to Column