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
0
votes
0 answers

Specific Crosstab

I'm facing a problem with some data, so i need to use a crosstab to generate a specific report, could you help me? Basicaly i have one query select like this: select route_id,id,tipo,local,valor_cobrado from itinerario and inside this table i have…
Ademir Gomes
  • 11
  • 1
  • 4
0
votes
1 answer

Query has no result in destination data when calling colpivot inside pgsql stored procedure

I have created a procedure to generate temp table using colpivot https://github.com/hnsl/colpivot and saving the result into a physical table as below in PGSQL create or replace procedure create_report_table() language plpgsql as $$ begin drop…
Nilaksha Perera
  • 715
  • 2
  • 12
  • 36
0
votes
1 answer

Postgres - SQL query to extract a cross-tab like functionality from many-to-many mapping

I have a data-set consisting of Student and Subject in a PostgreSQL Database. The relations look something like: Student: id, name, ... Student ID | Name | ... 1 | Ramesh | ... 2 | Suresh |…
Curious Coder
  • 646
  • 8
  • 18
0
votes
1 answer

Function with Pivot (crosstab) on unknown rows postgressql

I have a table similar to this: (Postgressql v.10.8) id ref street city total year 1 2077 Burban Str. London 10000 2010 2 2077 Burban Str. London 12000 2011 3 2077 Burban Str. London 14000 2012 4 2077 …
user2210516
  • 613
  • 3
  • 15
  • 32
0
votes
1 answer

Flatten Postgers left join query result with dynamic values into one row

I have two tables products and product_attributs. One Product can have one or many attributs and these are filled by a dynamic web form (name and value inputs) added by the user as needed. For example for a drill the user could decide to add two…
Didus
  • 1
0
votes
1 answer

CrossTab function posgresql

I am new to postgresql and even newer to crosstab but from what I have read, the below query should work: select * from crosstab( $$select distinct "AccountNumber" , "ProductCategory", sum("ProductQuantity") sm from "MsfDataRecords" mdr group by…
StuP
  • 45
  • 1
  • 6
0
votes
1 answer

Duplicate Category Name Error 42710 in Crosstab query Postgres

I am having a tough time getting my crosstab query to execute. I keep getting "Duplicate Category Name Error 42710" when I run the following query. SELECT * FROM crosstab ( $$ Select date_year, …
Imron
  • 3
  • 1
0
votes
2 answers

How to Use Crosstab with dynamic number of columns by making use of Join Clause In PostgreSQL

I am trying to get Table Data in a vertical way using Crosstab. To be more precise, I need to group by prefix and postfix at the same time and get summ. In this case I should Join 2 tables and get names for unique prefix and postfix. Number of…
Abdusoli
  • 661
  • 1
  • 8
  • 24
0
votes
1 answer

2 Level pivot using Postgresql

I have a table whose schema along with data (table_name : raw_data) appears to be this : name | category | clear_date | A | GOOD | 2020-05-30 | A | GOOD | 2020-05-30 | A | GOOD | 2020-05-30 | A | GOOD | 2020-05-30 | A …
Aman Raparia
  • 494
  • 1
  • 5
  • 13
0
votes
0 answers

Generate dynamic columns for CROSSTAB in postgresql?

I have this table in postgres CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1'); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2'); INSERT…
John
  • 276
  • 1
  • 9
  • 29
0
votes
0 answers

View Rows as columns in postgres SQL without using cross Tab as Cross Tab is provioing the excepted results

I am using Postgres 9.6. and i have a result set like this: employee Name|collegeName | Date |attendance -------------|------------|----------|----------- employee1 |college1 |2020-05-01| true employee1 |college2 |2020-05-01|…
user13564809
  • 1
  • 1
-1
votes
1 answer

Count variation in the output

SELECT * FROM crosstab( 'SELECT request_address,CASE WHEN code = $$200$$ THEN $$success$$ ELSE $$failure$$ END AS code,id,count(*) as count from table_1 group by 2,1,3 order by 2,1,3', $$VALUES…
Ramya Mahe
  • 19
  • 2
1
2