Difference between revisions of "Rows to columns"

From SQLZOO
Jump to: navigation, search
 
(8 intermediate revisions by 2 users not shown)
Line 1: Line 1:
Display rows as columns
+
<p>Display rows as columns</p>
 +
<p>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.</p>
 +
<p>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.</p>
 +
<div class="ref_section">
 +
<table class= "db_ref">
 +
<caption>Table 1</caption>
 +
<tr><th align='center'>'''student'''</th><th>'''course'''</th><th align='center'>'''grade'''</th></tr>
 +
<tr><td align='left'>Gao Cong</td><td align='left'>Java</td><td align='left'>80</td></tr>
 +
<tr><td align='left'>Gao Cong</td><td align='left'>Database</td><td align='left'>77</td></tr>
 +
<tr><td align='left'>Gao Cong</td><td align='left'>Algebra</td><td align='left'>50</td></tr>
 +
<tr><td align='left'>Dongyan Zhou</td><td align='left'>Java</td><td align='left'>62</td></tr>
 +
<tr><td align='left'>Dongyan Zhou</td><td align='left'>Database</td><td align='left'>95</td></tr>
 +
<tr><td align='left'>Dongyan Zhou</td><td align='left'>Algebra</td><td align='left'>62</td></tr>
 +
</table>
 +
<table class="db_ref">
 +
<caption>Table 2</caption>
 +
<tr><th align='center'>'''name'''</th><th>'''java'''</th><th align='center'>'''DB'''</th><th align='center'>'''Algebra'''</th></tr>
 +
<tr><td align='left'>Gao Cong</td><td align='left'>80</td><td align='left'>77</td><td align='left'>50</td></tr>
 +
<tr><td align='left'>Dongyan Zhou</td><td align='left'>62</td><td align='left'>95</td><td align='left'>62</td></tr>
 +
</table>
 +
</div>
 
<div class='ht'>
 
<div class='ht'>
 
<div class=params>schema:scott</div>
 
<div class=params>schema:scott</div>
 
<source lang=sql class='tidy'>DROP TABLE exam;
 
<source lang=sql class='tidy'>DROP TABLE exam;
 
DROP TABLE courseGrade;</source>
 
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,
 
   Java INTEGER,
   Database INTEGER,
+
   dbt INTEGER,
 
   Algebra INTEGER );
 
   Algebra INTEGER );
 
INSERT INTO exam VALUES ('Gao Cong',80,77,50);
 
INSERT INTO exam VALUES ('Gao Cong',80,77,50);
Line 14: Line 34:
 
   student VARCHAR(20),
 
   student VARCHAR(20),
 
   course VARCHAR(20),
 
   course VARCHAR(20),
   grade INTEGER);
+
   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>
 
</source>
 
<div>
 
<div>
 
<p>To swap rows into columns you could use either a self join(in the example) or
 
<p>To swap rows into columns you could use either a self join(in the example) or
 
you can use <code>CASE</code></p>
 
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 myStudents JOIN courseGrade  
+
  FROM exam JOIN courseGrade  
  ON (name=student)
+
        ON (name=student)
GROUP BY name
+
  GROUP BY name</pre>
<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>
 +
<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
 
  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>
 
</div>
  
<source lang='sql' class='def'>SELECT name, java.grade AS java,
+
<source lang='sql' class='def'>
 +
SELECT name, java.grade AS java,
 
db.grade AS DB, alg.grade AS Algebra
 
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')
 
  (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')
 
  (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>
 
  (name=alg.student AND alg.course='Algebra')</source>
 
<div class="ecomm e-mysql" style="display: none"></div>
 
<div class="ecomm e-mysql" style="display: none"></div>

Latest revision as of 15:15, 2 August 2012

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
studentcoursegrade
Gao CongJava80
Gao CongDatabase77
Gao CongAlgebra50
Dongyan ZhouJava62
Dongyan ZhouDatabase95
Dongyan ZhouAlgebra62
Table 2
namejavaDBAlgebra
Gao Cong807750
Dongyan Zhou629562
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')
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense