I have a query in Postgres. It’s working well and returns exactly the rows I’m looking for. Here’s the query: This is what it returns: id status timestamp 1 started 2022-01-15 05:12:36 1 paused 2022-04-14 09:12:50 1 killed 2022-04-27 13:12:48 How can I pivot this table to return this exactly…
Tag: crosstab
How to rotate a two-column table?
This might be a novice question – I’m still learning. I’m on PostgreSQL 9.6 with the following query: My query returns the following dynamic rows: locales count en 10 fr 7 de 3 n additional locales (~300)… n-count I’m trying to rotate it so that locale values end up as columns with a s…
crosstab in PostgreSQL
I have the following tables: and How can I query the database so that it returns the table where n ranges over the rows of the table sensor and ‘abbrv $i’ is replaced by the corresponding value in the table sensor? The following query works up to a certain extent: I need to know how many rows ther…
Pivot table without crosstab/tablefunc
I have a table like this INPUT I want to be able to pivot this table in the following manner OUTPUT All the articles that I can find online pivot tables based on a second column. My ultimate goal is to get one records per ID. FINAL OUTPUT Here the values of the size, python, cpp, non-code columns are aggregat…
Pivoting SQL table with crosstab function
I made this query that lists all real estate prices per m2 per year in a city. It works nice yet all years end up in rows, with the prices behind them. I would prefer seeing the years in columns with …
How can I get a record to be counted in multiple columns of a Crosstab Query?
Background information: My company requires employees to maintain at least one certification (cert) on a position. There are a total of 17 different certifications that an employee can get. An …
showing columns in a particular pattern in postgresql
i have written a query which returns a table showing monthly total working hours of each person in the company. so the result is: name*****jan************feb**********march ……… dec Tom 170:…
Getting Foreign Keys as separate columns
i’m not really much of an expert when it comes to databases and i was wondering if it’s possible to do something which might be a little weird I’m having a simple table called options which has 3 …
PostgreSQL says “return and sql tuple descriptions are incompatible”
I have the following data: ID CLASS VALUE 1 NHB 700905.7243 1 HBW 164216.1311 1 HBO 700905.7243 2 NHB 146023.3792 2 HBW 89543.2972 2 HBO 82152.072 3 NHB 1409818….
Pivot on Multiple Columns using Tablefunc
Has anyone used tablefunc to pivot on multiple variables as opposed to only using row name? The documentation notes: The “extra” columns are expected to be the same for all rows with the same row_name value. I’m not sure how to do this without combining the columns that I want to pivot on (w…