Rows to columns - Revision history

Connor at 15:15, 2 August 2012

2012-08-02T15:15:04Z

Connor at 15:14, 2 August 2012

2012-08-02T15:14:46Z

Connor at 15:14, 2 August 2012

2012-08-02T15:14:05Z

Connor at 13:49, 2 August 2012

2012-08-02T13:49:34Z

Connor at 09:46, 2 August 2012

2012-08-02T09:46:04Z

Marek: Undo revision 2929 by Marek (talk)

2012-08-01T15:40:41Z

Undo revision 2929 by Marek (talk)

Marek at 15:38, 1 August 2012

2012-08-01T15:38:38Z

Connor at 08:52, 31 July 2012

2012-07-31T08:52:17Z

Connor at 08:50, 31 July 2012

2012-07-31T08:50:53Z

Connor at 08:46, 31 July 2012

2012-07-31T08:46:40Z

@@ Line 51: / Line 51: @@
           THEN grade ELSE NULL END) AS Database,
         MAX(CASE WHEN course='Algebra'
-             THEN grade ELSE NULL END) AS Algebra
+         THEN grade ELSE NULL END) AS Algebra
    FROM exam JOIN courseGrade
           ON (name=student)

@@ Line 46: / Line 46: @@
 you can use <code>CASE</code></p>
 <pre>SELECT name,
-            MAX (CASE WHEN course='Java'
+       MAX (CASE WHEN course='Java'
-             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 Database,
+         THEN grade ELSE NULL END) AS Database,
-            MAX(CASE WHEN course='Algebra'
+       MAX(CASE WHEN course='Algebra'
               THEN grade ELSE NULL END) AS Algebra
-       FROM exam JOIN courseGrade
+  FROM exam JOIN courseGrade
           ON (name=student)
-       GROUP BY name</pre>
+  GROUP BY name</pre>
 <p>You can also do the opposite of this by displaying columns as rows.</p>
 <p>To do this you use a process that is the opposite of the swapping rows to columns one.

@@ Line 45: / Line 45: @@
 <p>To swap rows into columns you could use either a self join(in the example) or
 you can use <code>CASE</code></p>
- SELECT name,
+<pre>SELECT name,
- MAX (CASE WHEN course='Java'
+            MAX (CASE WHEN course='Java'
-  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 Database,
+             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
- FROM exam JOIN courseGrade
+       FROM exam JOIN courseGrade
-  ON (name=student)
+         ON (name=student)
- GROUP BY name
+       GROUP BY name</pre>
 <p>You can also do the opposite of this by displaying columns as rows.</p>
 <p>To do this you use a process that is the opposite of the swapping rows to columns one.
 The coding for this process is:</p>
   SELECT student, 'Java', Java FROM exam
- UNION SELECT student, 'Database', Database
+  UNION SELECT student, 'Database', Database
- FROM exam
+   FROM exam
- UNION SELECT student, 'Algebra', Algebra
+   UNION SELECT student, 'Algebra', Algebra
- FROM exam
+    FROM exam
 </div>
-<source lang='sql' class='def'>SELECT name, java.grade AS java,
+<source lang='sql' class='def'>
 db.grade AS DB, alg.grade AS Algebra
-FROM exam
+  FROM exam
- LEFT OUTER JOIN courseGrade java ON
+  LEFT OUTER JOIN courseGrade java ON
   (name=java.student AND java.course='Java')
- LEFT OUTER JOIN courseGrade db ON
+  LEFT OUTER JOIN courseGrade db ON
   (name=db.student AND db.course='Database')
- LEFT OUTER JOIN courseGrade alg ON
+  LEFT OUTER JOIN courseGrade alg ON
   (name=alg.student AND alg.course='Algebra')</source>
 <div class="ecomm e-mysql" style="display: none"></div>

@@ Line 1: / Line 1: @@
-Display rows as columns
+<p>Display rows as columns</p>
 <div class='ht'>
 <div class=params>schema:scott</div>
@@ Line 35: / Line 55: @@
    ON (name=student)
   GROUP BY name
-<p>You can also display columns as rows using:</p>
+<p>You can also do the opposite of this by displaying columns as rows.</p>
   SELECT student, 'Java', Java FROM exam
   UNION SELECT student, 'Database', Database

@@ Line 4: / Line 4: @@
 <source lang=sql class='tidy'>DROP TABLE exam;
 DROP TABLE courseGrade;</source>
-<source lang=sql class='setup'> CREATE TABLE exam(
+<source lang=sql class='setup'>CREATE TABLE exam (
-   Student VARCHAR(20),
+   name VARCHAR(20),
    Java INTEGER,
-   Database INTEGER,
+   dbt INTEGER,
    Algebra INTEGER );
 INSERT INTO exam VALUES ('Gao Cong',80,77,50);

@@ Line 14: / Line 14: @@
    student VARCHAR(20),
    course VARCHAR(20),
-   grade INTEGER);
+   grade INTEGER );
 INSERT INTO courseGrade VALUES ('Gao Cong','Java',80);
 INSERT INTO courseGrade VALUES ('Gao Cong','Database',77);
@@ Line 32: / Line 32: @@
   MAX(CASE WHEN course='Algebra'
    THEN grade ELSE NULL END) AS Algebra
-  FROM myStudents JOIN courseGrade
+  FROM exam JOIN courseGrade
    ON (name=student)
   GROUP BY name

@@ Line 15: / Line 15: @@
    course VARCHAR(20),
    grade INTEGER);
-INSERT INTO courseGrade VALUES ('Gao Cong',Java,80);
+INSERT INTO courseGrade VALUES ('Gao Cong','Java',80);
-INSERT INTO courseGrade VALUES ('Gao Cong',Database,77);
+INSERT INTO courseGrade VALUES ('Gao Cong','Database',77);
-INSERT INTO courseGrade VALUES ('Gao Cong',Algebra,50);
+INSERT INTO courseGrade VALUES ('Gao Cong','Algebra',50);
-INSERT INTO courseGrade VALUES ('Dongyan Zhou',Java,62);
+INSERT INTO courseGrade VALUES ('Dongyan Zhou','Java',62);
-INSERT INTO courseGrade VALUES ('Dongyan Zhou',Database,95);
+INSERT INTO courseGrade VALUES ('Dongyan Zhou','Database',95);
-INSERT INTO courseGrade VALUES ('Dongyan Zhou',Algebra,62);
+INSERT INTO courseGrade VALUES ('Dongyan Zhou','Algebra',62);
 </source>
 <div>