Rows to columns

Display rows as columns

In this example you are shown how to display your rows as columns, and below you are told how to do the opposite by being able to display columns as rows.

Table 1 displays the results as they are in the database and table 2 displays the results as how they should look when rows have been swapped with columns.

Table 1

student	course	grade
Gao Cong	Java	80
Gao Cong	Database	77
Gao Cong	Algebra	50
Dongyan Zhou	Java	62
Dongyan Zhou	Database	95
Dongyan Zhou	Algebra	62

Table 2

name	java	DB	Algebra
Gao Cong	80	77	50
Dongyan Zhou	62	95	62

schema:scott

DROP TABLE exam;
DROP TABLE courseGrade;

CREATE TABLE exam (
  name VARCHAR(20),
  Java INTEGER,
  dbt INTEGER,
  Algebra INTEGER );
INSERT INTO exam VALUES ('Gao Cong',80,77,50);
INSERT INTO exam VALUES ('Dongyan Zhou',62,95,62);
CREATE TABLE courseGrade (
  student VARCHAR(20),
  course VARCHAR(20),
  grade INTEGER );
INSERT INTO courseGrade VALUES ('Gao Cong','Java',80);
INSERT INTO courseGrade VALUES ('Gao Cong','Database',77);
INSERT INTO courseGrade VALUES ('Gao Cong','Algebra',50);
INSERT INTO courseGrade VALUES ('Dongyan Zhou','Java',62);
INSERT INTO courseGrade VALUES ('Dongyan Zhou','Database',95);
INSERT INTO courseGrade VALUES ('Dongyan Zhou','Algebra',62);

To swap rows into columns you could use either a self join(in the example) or you can use CASE

SELECT name,
       MAX (CASE WHEN course='Java'
        THEN grade ELSE NULL END) AS Java,
       MAX(CASE WHEN course='Database'
         THEN grade ELSE NULL END) AS Database,
       MAX(CASE WHEN course='Algebra'
         THEN grade ELSE NULL END) AS Algebra
  FROM exam JOIN courseGrade 
         ON (name=student)
  GROUP BY name

You can also do the opposite of this by displaying columns as rows.

To do this you use a process that is the opposite of the swapping rows to columns one. The coding for this process is:

SELECT student, 'Java', Java FROM exam
 UNION SELECT student, 'Database', Database 
  FROM exam
  UNION SELECT student, 'Algebra', Algebra 
   FROM exam

SELECT name, java.grade AS java,
db.grade AS DB, alg.grade AS Algebra
  FROM exam
  LEFT OUTER JOIN courseGrade java ON
 (name=java.student AND java.course='Java')
  LEFT OUTER JOIN courseGrade db ON
 (name=db.student AND db.course='Database')
  LEFT OUTER JOIN courseGrade alg ON
 (name=alg.student AND alg.course='Algebra')

SQL HACKS

Hack 10 Converting subqueries into joins

Hack 11 Converting aggregate subqueries into joins

Hack 14 Generate Combinations

Hack 16 Search for a String across columns

Hack 24 Multiply Across a Result Set

Hack 25 Keep a running total

Hack 25.5 Splitting and combining columns

Hack 26 Include the rows your JOIN forgot

Hack 30 Calculate the maximum/minimum of two fields

Hack 33 Get values and subtotals in one shot

Hack 50 Combine tables containing different data

Hack 51/52 Display rows as columns

Hack 55 Import Someone Else's Data

Hack 62 Issue Queries Without Using a Table

Hack 63 Generate rows without tables

Hack 70 Combine your queries

Hack 72 Extract a subset of the results

Hack 78 Break it down by Range

Hack 88 Test two values from a subquery

Hack 98 Find and stop long running queries

Hack 100 Run SQL from a Web Page