SQL QUERY: Can I get different row values and display it as different columns when testing query?

Question

this is a sample of the database I am working

I want something like this, displaying specific rows as a column

Answer 1

The problem here is that when you display a value from row B as a column attached to row A, you need to specify why is that value exactly from row B (rather than, say, row C). You can wing it to an extent, but this is increasingly deprecated in MySQL (and not possible in other flavors of SQL). For this same reason, if you GROUP BY some columns, you shouldn't specify a non-GROUP or non-aggregate value in the SELECT; otherwise it's ambiguous which value the engine should pick.

So what you ask is the same thing as doing a JOIN for each column:

SELECT maintable.*, 
    b.location_nr AS col2, 
    c.location_nr AS col4, 
    d.location_nr AS col5
FROM maintable
    LEFT JOIN maintable AS b ON (... AND b.type = 2)
    LEFT JOIN maintable AS c ON (... AND c.type = 4)
    LEFT JOIN maintable AS d ON (... AND d.type = 5)
WHERE maintable.type = 42;

So here you get all records of type 42, then, if one of these (Juan) has also a record of type 2 in the same table, then it will appear in column col2, and so on.

If such a row is not present, col2 will be valued NULL. If more than one row appears, then you will get multiple rows.

Another alternative:

SELECT ..., 
   (SELECT location_nr FROM maintable AS b WHERE ... AND b.type = 2) AS col2,
   ...

Here, too, you must supply your JOIN conditions for the secondary records to be picked up.

--

That said, you might probably be better off changing the database layout and adding an "attribute" table, only leaving the main information in the primary table.