SQL Query - make result rows into columns

Question

Long story short, I would like to take the results from the top part of the picture, and have them show up as the bottom does. The TestName field has 1300+ options, so I'm guessing a subquery for pulling the TestName and then creating a column based off of it would be best. This is for a Microsoft SQL Database.

Orginal Table:
+-----------------+--------------+------------------+
| AccessionNumber |   TestName   |      Result      |
+-----------------+--------------+------------------+
| K09035053       | Organism     | Rhodococcus equi |
| K09035053       | Timentin     | Susceptible      |
| K09035053       | Erythromycin | Susceptible      |
| K09035053       | Sulfa-Drugs  | Resistant        |
+-----------------+--------------+------------------+

Fixed Table:
+-----------------+------------------+-------------+--------------+-------------+
| AccessionNumber |     Organism     |  Timentin   | Erythromycin | Sulfa-Drugs |
+-----------------+------------------+-------------+--------------+-------------+
| K09035053       | Rhodococcus equi | Susceptible | Susceptible  | Resistant   |
+-----------------+------------------+-------------+--------------+-------------+

Please do not attach images. Copy contents into question and format properly to make it readable. It's pretty hard to copy text from image (f.e. for test purposes). Not to mention that image won't be available forever and people may read that question even after few years. — ElChupacabra, Feb 01 '18 at 21:25
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? "SQL" is just a query language, not the name of a specific database product. — , Feb 01 '18 at 21:56
Just SQL, running queries through the Management Studio, or rather a Microsoft SQL database. — D Miles, Feb 01 '18 at 21:58
Assuming this is in SQL, look into dynamic pivot. There's tons of guides here in SO. — Isaiah3015, Feb 01 '18 at 22:17
Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — JNevill, Feb 01 '18 at 22:22

score 1 · Answer 1 · answered Feb 01 '18 at 22:46

For MSQL, the below should work.

    CREATE TABLE STACKOVERFLOW
    ( 
    AccessionNumber nvarchar(max),
    TestName nvarchar(max),
    Result nvarchar(max)
    )

    INSERT INTO STACKOVERFLOW
    VALUES ('K09035053', 'Organism', 'Rhodococcus equi'),
    ('K09035053', 'Timentin', 'Susceptible'),
    ('K09035053', 'Erythromycin', 'Susceptible'),
    ('K09035053', 'Sulfa-Drugs', 'Resistant')

    SELECT * FROM STACKOVERFLOW

    SELECT * FROM
    (
    SELECT AccessionNumber, TestName, Result
    FROM STACKOVERFLOW) AS SOURCETABLE
    PIVOT
    (
      MAX(Result)
      FOR TestName In ([Organism], [Timentin], [Erythromycin], [Sulfa-Drugs])
    ) AS PIVOTTABLE

EDIT:

Just read that your TestName has many options, so you probably still want to use PIVOT but generate the SQL String dynamically in T-SQL and then executing that string.

SQL Query - make result rows into columns

1 Answers1