Difference between revisions of "Rows to columns"
| Line 4: | Line 4: | ||
<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 ( |
| − | + | name VARCHAR(20), | |
Java INTEGER, | Java 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); | ||
Revision as of 09:46, 2 August 2012
Display rows as columns
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 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')
Hack 10 Converting subqueries into joins
Hack 11 Converting aggregate subqueries into joins
Hack 16 Search for a String across columns
Hack 24 Multiply Across a Result Set
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 72 Extract a subset of the results
Hack 78 Break it down by Range
Hack 88 Test two values from a subquery
