9/22/2023 0 Comments Postgresql crosstabSELECT * from transform('art', 'SELECT * from art','S M L ','tgl','qty', '0') as foo(art varchar, S numeric, M numeric, L numeric) For example: if you need a crosstab with only th S M and L columns you can call the function with You are not obbliged to extract all of the columns of the tgl column. You will have to fiddle around a bit with the return types of the columns of the new table but it should not be difficut to find the correct ones out. SELECT * from transform('art', 'SELECT * from art','S M L XL XXL ','tgl','qty', '0') as foo(art varchar, S numeric, M numeric, L numeric, XL numeric, XXL numeric) You can also get the data back by calling: SELECT art,sum("S") as "S",sum("M") as "M",sum("L") as "L",sum("XL") as "XL",sum("XXL") as "XXL" FROM (SELECT art,case when tgl='S' then sum(qty) else 0 end as "S",case when tgl='M' then sum(qty) else 0 end as "M",case when tgl='L' then sum(qty) else 0 end as "L",case when tgl='XL' then sum(qty) else 0 end as "XL",case when tgl='XXL' then sum(qty) else 0 end as "XXL" from (SELECT * from art) as foo GROUP BY art,tgl) as bar group by art This will produce a string that you can copy and paste and will give you the above result. SELECT transformstr('art','SELECT * FROM art','S M L XL XXL ','tgl','qty', '0') This is how we will call the transformstr function This controls weather we want NULL or zero produced by the crosstab query where column values are null or 0 (But you can use any other value for this. use null to display nothing or 0 if you want zeros. This is the column that has to summed to produce the valuesĦ - null value. the column name of the column that contains the headers that will be producedĥ - sum_value. This is the query that will produce the data to be cross tabbedģ - pivot columns These are the headers that we want to be produced in the output tableĤ - data column name. This is the first column which the data will be pivoted aroundĢ - data to process from a query. Select transform('col1', 'select * from foo', 's m l xl xxl ,'null')ġ - pivot column. It is *VERY* important that in the column reference, the last piece of the csv must be a " " (semicolon) eg. Transform(text,text,text,text,text,text) -> This is a set returning function that will produce a result in psql Transformstr(text,text,text,text,text,text) -> This is a function that will produce a SQL string that will perform a crosstab query for you. Transform as in ms-access (Crosstab query). If you have any comments or suggestions please email me įunction by Fabrizio Mazzoni, veramente(at)libero(dot)it probably it could have a huge boost in performance. if anyone wants to rewrite it in C lease do it. Hope this can be a start for a new functionallity. Please test it out and decide if it can be used with the postgresql distribution. All the instructions with a very very simple test are included in the. I tested it on a couple of tables and it seems to work quite well. One returns a sql string which can then be executed and the other performs the sql from the function itself and produces output. The headers of the resulting query are passed as a csv string so it can grow without having to create a function for every crosstab query we want to execute. The difference between this and the one you already have in contrib is that this one has no limitations on the arguments passed to it.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |