Обсуждение: function to return rows as columns?
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.
Regards,
Miguel Angel.
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
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.
In response to Linos : > 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 Other solution with plain SQL: test=*# select * from linos ; item | size | stock ------+------+------- 123 | XL | 10 123 | XXL | 5 123 | XS | 3 (3 rows) test=*# select item, sum(case when size='XL' then stock else 0 end) as "XL", sum(case when size='XXL' then stock else 0 end) as "XXL", sum(case when size='XS' then stock else 0 end) as "XS" from linos where item=123 group by item; item | XL | XXL | XS ------+----+-----+---- 123 | 10 | 5 | 3 (1 row) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
A. Kretschmer escribió: > In response to Linos : >> 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 > > Other solution with plain SQL: > > test=*# select * from linos ; > item | size | stock > ------+------+------- > 123 | XL | 10 > 123 | XXL | 5 > 123 | XS | 3 > (3 rows) > > test=*# select item, sum(case when size='XL' then stock else 0 end) as > "XL", sum(case when size='XXL' then stock else 0 end) as "XXL", sum(case > when size='XS' then stock else 0 end) as "XS" from linos where item=123 > group by item; > item | XL | XXL | XS > ------+----+-----+---- > 123 | 10 | 5 | 3 > (1 row) > > > Andreas I think this approach have a problem (almost with my data), i have a somewhat large number of different sizes, about 200 or so (although i have a presented a limited example i now). Thanks anyway by the alternative way to do it Andreas. Regards, Miguel Angel.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > I think this approach have a problem (almost with my data), i have a > somewhat large number of different sizes, about 200 or so (although i > have a presented a limited example i now). Thanks anyway by the > alternative way to do it Andreas. > Actually the crosstab function won't work for you if you need something more then the basic one or two columns. (IIRC). Building a dynamic sql string such as Andreas suggested will always give you exactly what you want. Sim -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkmqp+wACgkQjDX6szCBa+r2IgCgpEGq1obqHtWcbbbdchm2cT1e ShEAniOg/qut+DXQS2mwvSvAMtiE7eOi =wKIf -----END PGP SIGNATURE-----