Rows to columns

From SQLZOO
Revision as of 15:29, 30 July 2012 by Connor (Talk | contribs)

Jump to: navigation, search

Display rows as columns

schema:scott
DROP TABLE Exam;
DROP TABLE courseGrade;
 CREATE TABLE exam(
  Student VARCHAR(20),
  Java INTEGER,
  DATABASE 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 myStudents JOIN courseGrade 
 ON (name=student)
GROUP BY name

You can also display columns as rows using

SELECT student, 'Java', Java FROM exam
UNION SELECT student, 'Database', Database 
FROM exam
UNION SELECT student, 'Algebra', Algebra 
FROM exam
 
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense