Search Results

I have a scenario where I have to pivot a list of rows to columns dynamically .For a particular order_number there might be several products. …

Following on from my previous question: Creating crosstab() pivot table in PostgreSQL 9.0 I managed to create a pivot table for ageband using the crosstab() function. … If this can be done then I can create dynamic views on different factors using one geometry-less table and unit_postcode. …

Given a time series of events where each event has a successful or unsuccessful outcome, how do I pivot the ratio of events by entity and time period columns with an aggregate array cell value? … [0 0 0] ... | +-----------------------------------------------+ To keep things simple, this report will never need more than 10 date window columns and I can dynamically template the SQL pivot …

generate the crosstab query with another query execute the crosstab query Or the problem must be changed to accept results embedded in a JSON column fed with json_object_agg (as illustrated in Static and dynamic … Dynamic alternative to pivot with CASE and GROUP BY provides quite a bit to read and pointers to other Q/As. …

B')::int as "B" ,bool_or(group_name='C')::int as "C" ,bool_or(group_name='Z')::int as "Z" FROM group_membership GROUP BY member ORDER BY member; Postgres isn't structured to make dynamic … pivot tables easy. …

See: Table name as a PostgreSQL function parameter Simple dynamic solution The difficulty is to return varying row types. SQL demands to know the return type at call time. … in this case): Dynamic alternative to pivot with CASE and GROUP BY …

DISTINCT column2) AS column2, count(DISTINCT column3) AS column3, count(DISTINCT column4) AS column4 FROM ecarroll.public.foo; Which is pretty much what you want, except you need to pivot … to generate dynamic SQL to do the count() wrap that with more dynamic sql to do the pivot. …

In general I recommend to do this kind of pivot/crosstab in the frontend (UI) Possible alternatives are to aggregate into a JSON value: select y, jsonb_object_agg(concat('x',x, 'z'), y) as xz from … Dynamic view example …

However, crosstab requires a custom type for the output of the pivot, with all field names and types that will go in column. I have all the information stored in the field table. … My question is then: can I create a custom type dynamically in PostgreSQL, from the information I store in my field table? …

There are some ways to avoid that: generate the query that pivots all columns with dynamic SQL. … With a client-side pivot (psql) A client-side, psql-only solution is more straightforward with \crosstabview (psql 9.6+). …

| A | B | C | D | -------------------- a | x | x | y | | b | x | z | | | c | w | | | | d | | | | | Unfortunately my SQL knowledge is rusty and Postgres does not support PIVOT …

Certainly not the most elegant way, but it works with dynamic sql: DO $$ DECLARE sql1 text := '''SELECT user_id AS row_name, field_name AS category, field_value AS value FROM user_data a JOIN … , sql2, ') AS ct(row_name int, ', sql3, ');'); EXECUTE sql4; END $$; SELECT * FROM tmp_data; All fields are typed it's reasonably fast with the small amount of data I have (less than 10k lines to pivot …

Detailed instructions: PostgreSQL Crosstab Query Still not "dynamic". A completely dynamic function is hardly possible since SQL demands to know the return type at the time of the function call. … If the return type is dynamic, you need two steps: build your query dynamically execute it. But there may be something for you here: Dynamic alternative to pivot with CASE and GROUP BY …

I think you are looking for something like pivot. PostgreSQL is able to do something like that with a tablefunc. … There you could use crosstab() But having said: This is not complete dynamic and therefore not fitting your question 100%. …

rdbms=postgres_11&fiddle=407a37686238bb3fbcbc4285d1705871 Unfortunately it turns out using a Pivot/CrossTab is much more complex than dynamically generating the original query with code: SELECT employeeid …