Received: from localhost (unknown [200.46.204.184])
	by developer.postgresql.org (Postfix) with ESMTP id 281972E0033
	for <pgsql-sql-postgresql.org@developer.postgresql.org>;
	Tue,  8 Apr 2008 23:11:40 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 92253-02
	for <pgsql-sql-postgresql.org@developer.postgresql.org>;
	Tue,  8 Apr 2008 23:11:29 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from www.postnewspapers.com.au (www.postnewspapers.com.au
	[202.61.230.242])
	by developer.postgresql.org (Postfix) with ESMTP id 9B9982E002C
	for <pgsql-sql@postgresql.org>; Tue,  8 Apr 2008 23:11:37 -0300 (ADT)
Received: from mail.postnewspapers.com.au
	(202-89-185-120.static.dsl.amnet.net.au [202.89.185.120])
	by www.postnewspapers.com.au (Postfix) with ESMTP id 0991AD5626;
	Wed,  9 Apr 2008 10:11:32 +0800 (WST)
Received: from [10.0.0.53] (wallace.localnet [10.0.0.53])
	by mail.postnewspapers.com.au (Postfix) with ESMTP id 9D0CD1E0434;
	Wed,  9 Apr 2008 10:11:34 +0800 (WST)
Message-ID: <47FC25DB.2090803@postnewspapers.com.au>
Date: Wed, 09 Apr 2008 10:11:39 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Icedove 1.5.0.12 (X11/20070607)
MIME-Version: 1.0
To: novice <user.postgresql@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: count question
References: <ddcb1c340804081851y245f4776g9de74b5715e3a3e@mail.gmail.com>
In-Reply-To: <ddcb1c340804081851y245f4776g9de74b5715e3a3e@mail.gmail.com>
X-Enigmail-Version: 0.94.2.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200804/55
X-Sequence-Number: 30594

novice wrote:

> Is it possible to write a query to produce:
> 
>  meter_id | no_of_bays |   bay_id
> ----------+------------+-----------
>      5397 |          2 |   5397-01
>      5397 |          2 |   5397-02
>      5409 |          3 |   5409-01
>      5409 |          3 |   5409-02
>      5409 |          3 |   5409-03

Sure. One way, not necessarily a particularly clever or efficient way,
is to do a join with:

generate_series(1, (select max(no_of_bays) from meter)) as i

and use a WHERE clause to select for `i <= no_of_bays'


eg:

SELECT
    meter_id, no_of_bays, meter_id::text||'-'||i::text AS bay_id
FROM meter,
     generate_series(1, (select max(no_of_bays) from meter)) as i
WHERE i <= no_of_bays;


--
Craig Ringer