Stack Overflow
Stack Overflow

Questions tagged [postgres-crosstab]

For questions related to the `crosstab()` functions in `tablefunc` module. Be sure to also include `postgresql` tag on your question.

crosstab() function produces a "pivot table" - a wide table where each row is a category and each column is a specific summary.

Resource:

27 questions
2
votes
2 answers

PIVOT/crosstab: I wan to return dynamic columns

I just want to return a dynamic columns out of cross tab, column names based out of query result. Header is lookup from different table like Types. any help appreciated. so far i could able to get hard coded columns. for example Result…
premshiva
  • 21
  • 1
  • 2
2
votes
2 answers

Pivot table using crosstab and count

I have to display a table like this: Year Month Delivered Not delivered Not Received 2021 Jan 10 86 75 2021 Feb 13 36 96 2021 March 49 7 61 2021 Apr 3 21 72 Using raw data generated by this query: SELECT year, TO_CHAR(…
sparkle
  • 7,530
  • 22
  • 69
  • 131
2
votes
1 answer

Postgresql Crosstab with Unknown Result

I'm trying create a Crosstab query to retrieve custom values from my database, and match them to a parent entity. I have the crosstab functioning, but the problem I have is that my results are entirely unknown. To explain, my application features a…
Curtis Snowden
  • 407
  • 1
  • 4
  • 20
1
vote
0 answers

Output of a sub-query as a input to a input to a column in select statement

with b as (SELECT distinct ip_address, FIRST_VALUE (id) OVER (PARTITION BY ip_address ORDER BY count DESC) AS max_id, FIRST_VALUE (count) OVER (PARTITION BY ip_address ORDER BY count DESC) AS max_count FROM (SELECT ip_address, id, count(*) FROM…
Ramya Mahe
  • 19
  • 2
1
vote
1 answer

Pivoting while grouping in postgres

I've been using crosstab in postgres to pivot a table, but am now needing to add in a grouping and I'm not sure if that's possible. I'm starting with results like this: Date Account# Type Count …
Mark
  • 71
  • 3
1
vote
2 answers

PostgreSQL: transpose multiple date columns dynamically to rows

My apologies if this has been asked before. I've searched the Postgres manual and many SO answers and still can't find a solution. I'm struggling to find the right SQL (postgres) command to convert the following table: | client | starts_on |…
PythonSherpa
  • 2,560
  • 3
  • 19
  • 40
0
votes
0 answers

Can I use the crosstab fxn when my table has this unique format?

Here's a sample of my table/results. I'm tracking whether these events were a direct or indirect result of a promotion sent. select * from test.generate_mkt_report(3379,'2001-01-01', current_date) limit 1; https://i.stack.imgur.com/FGSlw.png And…
Laura
  • 1
  • 2
0
votes
0 answers

PostgreSQL Dynamic crosstab query

I have a fact table like CREATE TABLE fact_table ( customer VARCHAR(50), product VARCHAR(50), city VARCHAR(50), measure INTEGER ); INSERT INTO fact_table (customer, product, city, measure) VALUES ('Oleg', 'Shampoo', 'Zurich', 5), …
DrDizzy
  • 1
0
votes
0 answers

PostgreSQL CROSSTAB incorrect output

Running into issues using CROSSTAB in PostgreSQL to transform data from long to wide. Here are the tables that I get before and after running CROSSTAB. Table returned from source…
Sarah Binder
  • 1
  • 1
0
votes
0 answers

Additional column with values as column identifier which has the highest value

SELECT *, 'success' as status FROM crosstab( 'SELECT ip_address,id,count(*) as count from table_1 where code= ''200'' group by 1,2 order by 1,2', 'select COALESCE(id,''null'') from public.table_1 group by id order by id asc') AS…
Ramya Mahe
  • 19
  • 2
0
votes
0 answers

Can Postgres crosstab() use temp files?

I'm trying to run crosstab() function on the amount of data, that doesn't fit RAM. I expected that Postgres will use temp files on disk, as with sorting after exhausting work_mem limit. But it doesn't seem to use them. My test case: Postgresql 15…
Pavel Tarasov
  • 31
  • 4
0
votes
0 answers

SQL Crosstab error :- return and sql tuple descriptions are incompatible

I have table as below and I am applying below CrossTab query on it. select * from crosstab ( 'SELECT dates,skillset, queued from hlfhrly order by 1,2') as ct ("dates" date,"skillset" text, "queued" bigint ) However I am getting below…
Gurpreet Singh
  • 1
0
votes
1 answer

Postgres query that shows the results from 2 columns with the highest counts per month

I have a database with about 2500 results from 2022. I'm trying to build queries that will show me the top industry and country results from each month throughout the entire year. The preferred output would include the top X (number) of each…
NeodymiumPhish
  • 27
  • 4
0
votes
0 answers

How Can I Convert oracle To PostgreSql?

I'm having a hard time changing the below query to Postgresql. Please help. Trying to use Postgresql's crosstab doesn't make sense. WITH temp AS ( SELECT TRTR.SHEET_DATA_ROW_ID ,TRTD.TEMPL_SHEET_COL_ID ,TRTD.COLUMN_VAL ,TRTR.ROW_NUM FROM ESBTRTS…
GILHOSHIN
  • 1
  • 1
0
votes
0 answers

extra column into a crosstab PostgreSQL query

I'm trying to get an extra column into a crosstab query in PostgreSQL The code I use is as shown SELECT * FROM crosstab( 'SELECT wkowono, /*wkoptno,*/ prowkc, CONCAT(wkowono,''-'',to_char(proopno,''FM000''),''-'',''1'') AS scan FROM…
martin freyne
  • 1
  • 1
1
2