Re: function to return rows as columns?

[Linos <info(at)linos(dot)es>]

Thomas Kellerer escribió:
> Linos, 27.02.2009 11:41:
> > Hello,
> >     i have a query that returns a result set like this:
> >
> > item | size | stock
> > 123  | XL   | 10
> > 123  | XXL | 5
> > 123  | XS   | 3
> >
> > and i would like get the results like this:
> >
> > item | XL | XXL | XS
> > 123  | 10  | 5     | 3
> >
> > i have been thinking how to do it with a plpgsql function but the 
> > number of sizes depend on the item that it is queried so i can not 
> > create a type and return it, i could create it like a text 
> > concatenating the stock and size of every row and returning the 
> > complete line text but i would have to process it in the application 
> > anyway so i am searching a solution that lets me return it like a record.
> >
> > I have been searching the list and maybe i could create the record 
> > type inside the function and to get the correct names and number of 
> > columns in the application side launching a query to get the number of 
> > sizes before call the function to specify the columns in the function 
> > call but maybe i am missing anything important here? any better (or 
> > more correct) way to do this? Thanks.
>
> Check out the "crosstab" function in the "Tablefunc" module:
>
> http://www.postgresql.org/docs/current/static/tablefunc.html

I knew i was missing something hehehe, thanks Thomas.