SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-19-2008, 06:02 PM
| ||||
| ||||
 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql      |
|
04-19-2008, 06:02 PM
| |||
| |||
| Re: count question 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql |
|
04-19-2008, 06:02 PM
| |||
| |||
| Re: count question 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql |
|
04-19-2008, 06:02 PM
| |||
| |||
| Re: count question 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql |
|
04-19-2008, 06:02 PM
| ||||
| ||||
| Re: count question Volkan YAZICI wrote: > 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. > May be this will help you ashish=# select meter_id,no_of_bays,meter_id||'-'||generate_series(1,no_of_bays) from meter; meter_id | no_of_bays | ?column? ----------+------------+---------- 5397 | 2 | 5397-1 5397 | 2 | 5397-2 5409 | 3 | 5409-1 5409 | 3 | 5409-2 5409 | 3 | 5409-3 With Regards Ashish ================================================== ================= 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 ================================================== ================= |
Linear Mode
