How can I return a query result vertically

Question

If I use the following UNION queries below I will get the result in several rows... i.e. vertically:

SELECT "Username"  AS "Attribute", username  AS "Value" FROM users WHERE id = 123
UNION 
SELECT "Email"     AS "Attribute", email     AS "Value" FROM users WHERE id = 123
UNION 
SELECT "Name"      AS "Attribute", name      AS "Value" FROM users WHERE id = 123
UNION 
SELECT "Activated" AS "Attribute", activated AS "Value" FROM users WHERE id = 123;

This will result in a "vertical table", like this:

| Attribute | Value               |
| --------- | ------------------- |
| Username  | UserStephen         |
| Email     | stephen@example.com |
| Name      | Stephen Jones       |
| Activated | true                |

This works fine of course, but I wonder if there are other methods avaialable in MySQL/MariaDB? If I have 10+ attributes it will be a lot of UNION SELECTs, and a lot of FROM users WHERE id = 123. I.e. the query becomes quite large, and perhaps ineffective(?).

Can I optimize or simplify the query?

Additional comment: I have a PHP script where I present the result from any query in an HTML table format. I.e. in rows & columns, just as it is returned from the query. In some cases I wan't to present the result vertically, and therefore I want a query that returns the result like this, instead of the fields in columns. I don't want to have to rewrite the PHP script to transpose the table in different situations.

Why do you need this? And is this: [How to show records vertically in mysql command line?](https://stackoverflow.com/questions/11134569/how-to-show-records-vertically-in-mysql-command-line) an option? (Please use [edit] to specify why this is not an option, which I think is the reason for asking this question?) — Luuk, Feb 25 '23 at 10:20
@Luuk I have added a comment in the question. It is related on how the query result shall be presented in HTML in PHP. But my question is more focused on the query, rather than the PHP coding — Gowire, Feb 25 '23 at 10:29
It' still unclear (to me) what the difference between your long query, and a simple `SELECT username, email, name, activated FROM users WHERE id = 123` is. The results should be the same, and formatting should be left over to PHP, which should not be a difficult problem to do in one (re-usable) function. — Luuk, Feb 25 '23 at 10:35
@luuk Perhaps you're right. The more I think about it, and the more I struggle with a more complex query, I tend to aggree :) — Gowire, Feb 25 '23 at 10:38

How can I return a query result vertically

0 Answers0