# 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,
)
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
UNPIVOT_COLUMN('subject')