Transposing columns using postrgesql - displaying data vertically rather than hortizontally

[conif]

I am using the Retool Database features for now with SQL queries. The data is being displayed in a horizontal table. There are so many columns that the user will need to scroll horizontally to read the last ones. In addition, some of the fields contain several sentences of text and the user will have to increase the column width for those fields if they need to see the entire content.

I would like the user to be able to see all the pairs without needing to scroll or widen column lengths.

Is there a way to display the items in a vertical table? I would then display the results in two tables, the first showing key/values 1-10 and hiding 11-20 and the second showing key/values 11-20 and hiding 1-10.

note: Values are displayed in various formats: text, numbers and dates etc.

[bradlymathews]

If you are displaying multiple columns, you need the Table. Key Value can only show two columns, the Key and the Value.

The Table also has a Cell Tooltip Add On that will show the entire text on hover so you don't have to make your column super wide.

I am not clear by what you mean by vertical table. A few ways this has been interpreted in the past. Maybe you can sketch out what you have in mind for your UI and share it here?

You have full control over your datatypes and how they are displayed in a Table. You can do that with a little more work in a Key Value - you just have to transform your data first.

[conif]

Thanks @bradlymathews . I want to show the columns vertically rather than horizontally to take advantage of screen real estate as you can see in the bottom table versus the top table:

I did some research and it looks like a SQL Cross Join lateral query can do this with some of the data:

select users_sample_data.id, t.*
FROM users_sample_data
CROSS JOIN LATERAL (
VALUES
('first_name', users_sample_data.first_name),
('last_name', users_sample_data.last_name),
('email', users_sample_data.email)

) as t(column_name, column_value)
ORDER BY id, column_name DESC;

But then when i try to add integers or booleans such as the Enabled column, I get a SQL error: VALUES types text and boolean cannot be matched

[bradlymathews]

Chat GPT suggests this:

"In Postgres how do I create a query that returns the results of a single row as a key/value pair where the key of the field name."

ChatGPT

In PostgreSQL, you can achieve this using the jsonb_object_agg function along with the jsonb_build_object function to convert a single row's columns into key/value pairs.

Let's say you have a table called my_table and it has columns col1, col2, and col3. Here's a way to turn a single row's columns into key/value pairs:

sqlCopy code

WITH CTE AS (
    SELECT
        jsonb_build_object('col1', col1) AS col1,
        jsonb_build_object('col2', col2) AS col2,
        jsonb_build_object('col3', col3) AS col3
    FROM
        my_table
    LIMIT 1  -- Assuming you want just one row.
)

SELECT
    jsonb_object_agg(key, value)
FROM
    CTE,
    jsonb_each(to_jsonb(CTE.*))
;

This query will return a single JSONB object with each column from the row as a key and the corresponding value as the value.

Do note that if my_table contains more rows, the LIMIT 1 will only take one of them, but which one is not predictable unless you add an ORDER BY clause. Adjust the query according to your requirements.

Me again. I assume you will need to have a where clause of some sort to only return one specific row rather than a dumb Limit 1

[conif]

Hey@bradleymathews thanks for this. I used another chat GPT suggestion to get an array of objects.

Then I created an additional SQL query and strung together where clauses to choose which array objects I wanted to show as rows-- I only wanted to show some of them. When I strung together a small number of "where "queries", I got back an array of objects but the order of the array does not transfer to the order of rows. In other words the fourth where clause is not always the fourth row. Is there any way to set a specific order of the rows?

[bradlymathews]

You can add an order by clause to the end. The order or operations in the where clause has no bearing on the order of the rows. For a simple query like this the database will usually (but not always) return the rows in the order they are physically on the hard drive, which is usually the order they were added to the table.

ORDER BY column_name

will tell it in what specific order you want the returned result.