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

Teradata: Transpose Column To Rows

Sometime it is required to transpose table columns into rows. This may be required for various business requirement and better data management. Lets try to achieve this with the help of a table named 'student' which contains student's marks in column and student's marks need to converted to rows.

Student Table: First creating a sample volatile table student as shown below

CREATE  VOLATILE TABLE student
( 
  id      INTEGER,
  name    VARCHAR(10),
  english INTEGER,
  maths   INTEGER,
  science INTEGER
)
ON COMMIT PRESERVE ROWS;

Data Population: Inserting couple of records in the table for transposing them to rows

INSERT INTO student(123,'Harry',90,95,95);
INSERT INTO student(345,'Porter',70,80,90);

Data View: Checking data in the student table

SELECT * FROM student;

Output:
id  name   english maths science
123 Harry   90       95       95
345 Porter  70       80       90

There can be multiple approaches (slightly different from one another) on how rows can be transposed.

Approach 1: By creating number sequence table with rows equivalent to the number of columns to be transposed to rows.

CREATE VOLATILE TABLE subject_sequence (seq_id INT) ON COMMIT PRESERVE ROWS;

INSERT INTO subject_sequence(1);
INSERT INTO subject_sequence(2);
INSERT INTO subject_sequence(3);

Approach 1 Solution: Below query can transpose columns to row using subject_sequence table's rows.

SELECT
  id,name,
  CASE seq_id 
     WHEN 1 THEN 'english'
     WHEN 2 THEN 'maths'
     WHEN 3 THEN 'science'
  END AS subject, 
  CASE seq_id 
     WHEN 1 THEN english
     WHEN 2 THEN maths
     WHEN 3 THEN science
  END AS marks
FROM student CROSS JOIN subject_sequence;

Output:
  id  name     subject   marks
----  -------  --------  -----
 123  Harry    english      90
 123  Harry    maths        95
 123  Harry    science      95
 345  Porter   english      70
 345  Porter   maths        80
 345  Porter   science      90

Approach 2: By creating a table with 1 row for equivalent to the column name which need to be transposed to rows.

CREATE VOLATILE TABLE subjects ( subject VARCHAR(10))  ON COMMIT PRESERVE ROWS;

INSERT INTO subjects ('english');
INSERT INTO subjects ('maths');
INSERT INTO subjects ('science');

Approach 2 Solution: Below query will also transpose columns to row and will give exactly same output as above

SELECT
  stu.id,
  stu.name,
  sub.subject,
  CASE sub.subject
     WHEN 'english' THEN stu.english
     WHEN 'maths'   THEN stu.maths
     WHEN 'science' THEN stu.science
  END AS marks
FROM student stu CROSS JOIN subjects sub;

Output:
  id  name     subject   marks
----  -------  --------  -----
 123  Harry    english      90
 123  Harry    maths        95
 123  Harry    science      95
 345  Porter   english      70
 345  Porter   maths        80
 345  Porter   science      90

Approach 3: Using Teradata's Unpivot function

Approach 3 Solution: Below query using TD_UNPIVOT will also transpose columns to row and will give exactly same output as with other 2 approaches.

SELECT * FROM TD_UNPIVOT(
ON( SELECT * FROM student)
USING
VALUE_COLUMNS('Marks')
UNPIVOT_COLUMN('subject')
COLUMN_LIST('english', 'maths', 'science')
COLUMN_ALIAS_LIST('english', 'maths', 'science' )
)X;


Output:
  id  name     subject   marks
----  -------  --------  -----
 123  Harry    english      90
 123  Harry    maths        95
 123  Harry    science      95
 345  Porter   english      70
 345  Porter   maths        80
 345  Porter   science      90

Using TD_UNPIVOT function with more groups of columns

Student Table: Creating a sample volatile table student with 2 columns for different pattern to be transposed as shown below.

CREATE  VOLATILE TABLE student
( 
  id             INTEGER,
  name           VARCHAR(10),
  english_marks  INTEGER,
  maths_marks    INTEGER,
  science_marks  INTEGER,
  english_grade  CHAR(1),
  maths_grade    CHAR(1),
  science_grade CHAR(1)
)
ON COMMIT PRESERVE ROWS;

Data Population: Inserting couple of records in the table for transposing them to rows.

INSERT INTO student(123,'Harry',90,95,95,'A','B','B');
INSERT INTO student(345,'Porter',70,80,90,'C','D','B');

Approach Solution: Below query using TD_UNPIVOT will also transpose columns to rows for the multi-columns.

SELECT * FROM TD_UNPIVOT(
ON( SELECT * FROM student)
USING
VALUE_COLUMNS('Marks','Grades')
UNPIVOT_COLUMN('subject')
COLUMN_LIST('english_marks,english_grade', 'maths_marks,maths_grade', 'science_marks,science_grade')
COLUMN_ALIAS_LIST('english', 'maths', 'science' )
)X;

Output:
   id  name     subject   marks Grades
 ----  -------  --------  ----- ------
  123  Harry    english      90      A
  123  Harry    maths        95      B
  123  Harry    science      95      B
  345  Porter   english      70      C
  345  Porter   maths        80      D
  345  Porter   science      90      B