Difference between revisions of "Rows to columns"

From SQLZOO
Jump to: navigation, search
(Undo revision 2929 by Marek (talk))
Line 7: Line 7:
 
   Student VARCHAR(20),
 
   Student VARCHAR(20),
 
   Java INTEGER,
 
   Java INTEGER,
   EDatabase INTEGER,
+
   Database INTEGER,
 
   Algebra INTEGER );
 
   Algebra INTEGER );
 
INSERT INTO exam VALUES ('Gao Cong',80,77,50);
 
INSERT INTO exam VALUES ('Gao Cong',80,77,50);
Line 29: Line 29:
 
   THEN grade ELSE NULL END) AS Java,
 
   THEN grade ELSE NULL END) AS Java,
 
  MAX(CASE WHEN course='Database'
 
  MAX(CASE WHEN course='Database'
   THEN grade ELSE NULL END) AS EDatabase,
+
   THEN grade ELSE NULL END) AS Database,
 
  MAX(CASE WHEN course='Algebra'
 
  MAX(CASE WHEN course='Algebra'
 
   THEN grade ELSE NULL END) AS Algebra
 
   THEN grade ELSE NULL END) AS Algebra
Line 37: Line 37:
 
<p>You can also display columns as rows using:</p>
 
<p>You can also display columns as rows using:</p>
 
  SELECT student, 'Java', Java FROM exam
 
  SELECT student, 'Java', Java FROM exam
  UNION SELECT student, 'Database', EDatabase
+
  UNION SELECT student, 'Database', Database
 
  FROM exam
 
  FROM exam
 
  UNION SELECT student, 'Algebra', Algebra  
 
  UNION SELECT student, 'Algebra', Algebra  

Revision as of 15:40, 1 August 2012

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 exam 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
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')
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense