Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f4MEdkA88656 for ; Tue, 22 May 2001 10:39:46 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4L7RaA71350 for ; Mon, 21 May 2001 03:27:39 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f4L7KH161748 for pgsql-sql@postgresql.org; Mon, 21 May 2001 03:20:17 -0400 (EDT) (envelope-from news) From: =?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Transposing data Date: Mon, 21 May 2001 09:23:06 +0200 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 66 Message-ID: <3B08C25A.17F695BD@cybertec.at> References: <3B081E2D.BB24DBE3@cybertec.at> <9ead2o$1rd7$1@news.tht.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org X-Mailer: Mozilla 4.77 [de] (X11; U; Linux 2.4.2-2 i686) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200105/242 X-Sequence-Number: 3060 I have also thought of a solution like that but the problem is that I do not have a complete list of all values occuring in the column so it has to be dynamical. The second problem is that the amount of data involved may become huge. Hans Alexander Dederer schrieb: > Hans-J?rgen Sch?nig wrote: > > > I want the values in column label to be displayed in the a-axis. Is > > there an easy way to transform the data: > > Here is the input data: > > age_code | label | count > > ----------+-------+------- > > age_1 | 30k | 1 > > age_1 | 50k | 2 > > age_1 | more | 2 > > age_2 | 40k | 2 > > age_3 | 40k | 1 > > > > I want the result to be: > > > > age_code | 30k | 40k | 50k | more > > ----------------------- > > age_1 | 1 | | 2 | 1 > > age_2 | | 2 | > > age_3 | | 1 | | > > > > Is there any easy way to do the job or do I have to write a PL/pgSQL > > function? > > Got it: > # SELECT * FROM aaa; > age_code | label | count > ----------+-------+------- > age_1 | 30k | 1 > age_1 | 50k | 2 > age_1 | more | 2 > age_2 | 40k | 2 > age_3 | 40k | 1 > > ------- > SELECT > s0.age_code, > (SELECT count FROM aaa s1 WHERE s1.age_code = s0.age_code AND s1.label = > '30k') as "30k", > (SELECT count FROM aaa s1 WHERE s1.age_code = s0.age_code AND s1.label = > '40k') as "40k", > (SELECT count FROM aaa s1 WHERE s1.age_code = s0.age_code AND s1.label = > '50k') as "50k", > (SELECT count FROM aaa s1 WHERE s1.age_code = s0.age_code AND s1.label = > 'more') as "more" > FROM aaa s0 > GROUP BY s0.age_code; > > age_code | 30k | 40k | 50k | more > ----------+-----+-----+-----+------ > age_1 | 1 | | 2 | 2 > age_2 | | 2 | | > age_3 | | 1 | | > (3 rows) > > Alexander Dederer.