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

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