How do i turn column results into column headers in SQL?

Question

I have the following tables:

Sample
  id PK

Test
  id PK
  name varchar

Section
  id PK
  name varchar


TestSection
  id PK
  test_id FK
  section_id FK

SampleTestResult
  id PK
  sample_id FK
  test_section_id FK
  result

When I do the following query:

  SELECT DISTINCT
   s.id as sample_id, ts.name as test, str.result
  FROM sample s 
  JOIN sample_test_result str ON s.id=str.sample_id
  JOIN test_section ts ON str.test_section_id=ts.id
  JOIN section sec ON ts.section_id=sec.id

I get a table that looks like this:

| sample_id | test       | result    |
--------------------------------------
|    1      |  t1        |   fail    |
|    1      |  t2        |   pos     |      
|    2      |  t1        |   neg     |
|    2      |  t3        |   rpt     |
|    3      |  t2        |   pos     |
|    3      |  t4        |   pos     |

However I would like to end up with a table like this:

| sample_id | t1    | t2    | t3    | t4    |
--------------------------------------------
|    1      | fail  | pos   | NULL  | NULL  |
|    2      | neg   | NULL  | rpt   | NULL  |
|    3      | NULL  | pos   | NULL  | pos   |

How can I transpose the table in SQL - is this possible in a query? Or does it have to be an sql view?

score 1 · Accepted Answer · answered Feb 15 '19 at 17:30

With MySQL, the typical way to solve this is to use conditional aggregation :

SELECT 
    s.id as sample_id,  
    MAX(CASE WHEN ts.name = 't1' THEN str.result END) as t1,
    MAX(CASE WHEN ts.name = 't2' THEN str.result END) as t2,
    MAX(CASE WHEN ts.name = 't3' THEN str.result END) as t3,
    MAX(CASE WHEN ts.name = 't4' THEN str.result END) as t4 
FROM 
    sample s 
    JOIN sample_test_result str ON s.id=str.sample_id
    JOIN test_section ts ON str.test_section_id=ts.id
    JOIN section sec ON ts.section_id=sec.id
GROUP BY s.id

score 1 · Answer 2 · answered Feb 15 '19 at 17:30

You want conditional aggregation :

SELECT s.id as sample_id, 
       MAX(CASE WHEN ts.name = 't1' THEN str.result END) as t1,
       . . . 
FROM sample s JOIN 
     sample_test_result str 
     ON s.id=str.sample_id JOIN 
     test_section ts 
     ON str.test_section_id=ts.id
     JOIN section sec ON ts.section_id=sec.id
GROUP BY s.id;

How do i turn column results into column headers in SQL?

2 Answers2