Thread: count question
i have a table CREATE TABLE meter ( meter_id integer NOT NULL, no_of_bays integer, CONSTRAINT meter_pkey PRIMARY KEY (meter_id) ) INSERT INTO meter( meter_id, no_of_bays) VALUES (5397, 2); INSERT INTO meter( meter_id, no_of_bays) VALUES (5409, 3); select meter_id, no_of_bays from meter; meter_id | no_of_bays ----------+------------ 5397 | 2 5409 | 3 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
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
On 4/8/08, Craig Ringer <craig@postnewspapers.com.au> wrote: > 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' You have a problem, and it's bad design. A bad design leads to these types of kludges. Consider re-engineering your schema. In any case, good luck.
On Wed, 9 Apr 2008, novice <user.postgresql@gmail.com> writes: > 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 Shouldn't this function be the responsitibility of client software, instead of database? Regards.
Volkan YAZICI wrote: <blockquote cite="mid:87ej9fr3kh.fsf@alamut.mobiliz.com.tr" type="cite"><pre wrap="">On Wed, 9 Apr2008, novice <a class="moz-txt-link-rfc2396E" href="mailto:user.postgresql@gmail.com"><user.postgresql@gmail.com></a>writes: </pre><blockquote type="cite"><pre wrap="">Isit 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 </pre></blockquote><pre wrap=""> Shouldn't this function be the responsitibility of client software, instead of database? Regards. </pre></blockquote><br /> May be this will help you<br /><br /><br /> ashish=# select meter_id,no_of_bays,meter_id||'-'||generate_series(1,no_of_bays)from meter;<br /> meter_id | no_of_bays | ?column?<br />----------+------------+----------<br /> 5397 | 2 | 5397-1<br /> 5397 | 2 | 5397-2<br /> 5409 | 3 | 5409-1<br /> 5409 | 3 | 5409-2<br /> 5409 | 3 | 5409-3<br /><br /> WithRegards<br /> Ashish<br /><pre> =================================================================== sms START NETCORE to 575758 to get updates on Netcore's enterprise products and services sms START MYTODAY to 09845398453 for more information on our mobile consumer services or go to http://www.mytodaysms.com =================================================================== </pre>