Re: Return and sql tuple descriptions are incompatible - Mailing list pgsql-general

Joe, that is exactly what I want.

Could you please give more detail example for this crosstab ?  I have
warehouse and product table like this :

CREATE TABLE tblwarehouse (
    id integer NOT NULL,
    warehousename character varying(20)
);

COPY tblwarehouse (id, warehousename) FROM stdin;
2    OFFICE
3    STORE2
\.

CREATE TABLE tblproduct (
    id serial NOT NULL,
    produkid text,
    warehouseid integer,
    onhand integer
);

COPY tblproduct (produkid, warehouseid, onhand) FROM stdin;
2791404000014    2    10
2791404000021    3    10
2791404000014    3    45
\.

I need crosstab query to display record from tblproduct like this :

   PRODUKID    | OFFICE | STORE2 | TOTAL
---------------+--------+--------+ ------
 2791404000014 |     10 |     45 |   55
 2791404000021 |      0 |     10 |   10

The crosstab warehouse column name is taken from tblwarehouse so when end
user add warehouse, crosstab column name will change automatically. And also
each row has total qty.

Please give detail command for this.

Thanks in advance !


-----Original Message-----
From: Joe Conway [mailto:mail@joeconway.com]
Sent: Wednesday, April 30, 2014 5:43 AM
To: Hengky Liwandouw
Subject: Re: [GENERAL] Return and sql tuple descriptions are incompatible

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/25/2014 11:55 PM, Hengky Liwandouw wrote:
> The last question : how to have automatically column header from
> other table ? say from select distinct warehousename from test
> order by 1 ?

If I understand your question, the best thing is to run the query
"select distinct warehousename from test order by 1" in your
application first, and then use the result to build a crosstab SQL
string. Then execute the crosstab SQL string with a second query.

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.14 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJTYBz6AAoJEDfy90M199hlybAP/0wfDVvJmvGcXK9lP0w+1vxR
A+Snl/E5MjUd3p9yTjBqP7MjDOgg467cn5gg+q7dtpya/jyED4Db78rn/G03ZqVK
2IVhaXQgD6p91w/s+zexdB7UBC3BxGzk/IMf3E93tlsZuBUk15x98jhY4FHl9Wgw
++luWY05pxnuluvmjwvc3e2PM99Re8EIw83KuiLzSYgChCvremz1uJi6hd0GDXme
iSmxhgn9blSL5hqJNsYWn0Ch0ga87T380HLOgFgnA4e9afE/8QU8kqHtLt+J9mKF
RgzvG2+cPCtlDmjTEWWbznKa+m54VmSnwwLjndU1JOsr4NEh4X5lv8Ahx6yh/BI0
PsoyU/DLrCJcXp263nUuGKbid+PRbRecpX5abX+fP/dfHPNqiw5ECFVpFMiZ35ug
5BqxJPX7hJAapwGp7QBKT9aFCtpuKFRkanywO19lgQC8MVXpRZH+/fADbzYrLc+d
v/9u6r4Qhxn7ltEjz7pU85EgZqYLw4j4fXRr1fZseN3+HXZpRVaBGC8JOyPE6Buc
p75tlgL7E6XXLNJsoY6RTqAcs3SmPgBBrmAfaP3etbpeHlZUBQMx9Xs2lOXWatn+
Uw3whFjJF1Wl8P+l5Bc49Yyerxj+d1Yb7Z3KOpLglOHi4K8hNu4knaeR1DiOs/4n
GLf3JS+5qijpX0aOndVK
=5sxK
-----END PGP SIGNATURE-----