Обсуждение: Results with leading zero
I have a table CREATE TABLE problem ( problem_id integer, solution_count integer ); INSERT INTO problem VALUES (1001, 4); INSERT INTO problem VALUES (1012, 11); SELECT * from problem; problem_id | solution_count ------------+--------------- 1001 | 4 1012 | 11 (2 rows) Is there a way I could write a query to produce the following? I will need the leading zero for solution < 10 problem_id | solution -------------+------------ 1001 | 01 1001 | 02 1001 | 02 1001 | 04 1012 | 01 1012 | 02 1012 | 03 1012 | 04 1012 | 05 1012 | 06 1012 | 07 1012 | 08 1012 | 09 1012| 10 1012 | 11 (15 rows) Thanks.
Am Mon, 16 Jun 2008 11:48:01 +1000 schrieb novice <user.postgresql@gmail.com>: You are watching for the lpad() function ... > I have a table > > CREATE TABLE problem ( > problem_id integer, > solution_count integer > ); > > INSERT INTO problem VALUES (1001, 4); > INSERT INTO problem VALUES (1012, 11); > > SELECT * from problem; > > problem_id | solution_count > ------------+--------------- > 1001 | 4 > 1012 | 11 > (2 rows) > > > Is there a way I could write a query to produce the following? I will > need the leading zero for solution < 10 > > problem_id | solution > -------------+------------ > 1001 | 01 > 1001 | 02 > 1001 | 02 > 1001 | 04 > 1012 | 01 > 1012 | 02 > 1012 | 03 > 1012 | 04 > 1012 | 05 > 1012 | 06 > 1012 | 07 > 1012 | 08 > 1012 | 09 > 1012 | 10 > 1012 | 11 > (15 rows) > > Thanks. >
am Mon, dem 16.06.2008, um 11:48:01 +1000 mailte novice folgendes: > I have a table > > CREATE TABLE problem ( > problem_id integer, > solution_count integer > ); > > INSERT INTO problem VALUES (1001, 4); > INSERT INTO problem VALUES (1012, 11); > > SELECT * from problem; > > problem_id | solution_count > ------------+--------------- > 1001 | 4 > 1012 | 11 > (2 rows) > > > Is there a way I could write a query to produce the following? I will Sure:select problem_id, generate_Series(1,solution_count) as solution_count from problem ; Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
am Mon, dem 16.06.2008, um 11:48:01 +1000 mailte novice folgendes: > I have a table > > CREATE TABLE problem ( > problem_id integer, > solution_count integer > ); > > INSERT INTO problem VALUES (1001, 4); > INSERT INTO problem VALUES (1012, 11); > > SELECT * from problem; > > problem_id | solution_count > ------------+--------------- > 1001 | 4 > 1012 | 11 > (2 rows) > > > Is there a way I could write a query to produce the following? I will > need the leading zero for solution < 10 > > problem_id | solution > -------------+------------ > 1001 | 01 > 1001 | 02 My previous answer was a little bit wrong (no leading zero for solution < 10), sorry. But no problem: select problem_id, to_char(generate_Series(1,solution_count),'09') as solution_count from problem ; Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Perfect! Thank you very mcuh :) 2008/6/16 A. Kretschmer <andreas.kretschmer@schollglas.com>: > am Mon, dem 16.06.2008, um 11:48:01 +1000 mailte novice folgendes: >> I have a table >> >> CREATE TABLE problem ( >> problem_id integer, >> solution_count integer >> ); >> >> INSERT INTO problem VALUES (1001, 4); >> INSERT INTO problem VALUES (1012, 11); >> >> SELECT * from problem; >> >> problem_id | solution_count >> ------------+--------------- >> 1001 | 4 >> 1012 | 11 >> (2 rows) >> >> >> Is there a way I could write a query to produce the following? I will >> need the leading zero for solution < 10 >> >> problem_id | solution >> -------------+------------ >> 1001 | 01 >> 1001 | 02 > > My previous answer was a little bit wrong (no leading zero for solution > < 10), sorry. But no problem: > > select problem_id, to_char(generate_Series(1,solution_count),'09') as solution_count from problem ; > > > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- THINK BEFORE YOU PRINT - Save paper if you don't really need to print this.
<p><font size="2">Hi,<br /><br /> Not to say which is better or worse, I find the use<br /> of lpad() interesting as well:<br/><br /> select problem_id,<br /> lpad (cast(solution_count as varchar), 9, '0')<br /> from problem;<br/><br /> Regards,<br /><br /> Tena Sakai<br /> tsakai@gallo.ucsf.edu<br /><br /><br /> -----Original Message-----<br/> From: pgsql-sql-owner@postgresql.org on behalf of novice<br /> Sent: Sun 6/15/2008 11:48 PM<br /> To: A.Kretschmer<br /> Cc: pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] Results with leading zero<br /><br /> Perfect!<br/> Thank you very mcuh :)<br /><br /> 2008/6/16 A. Kretschmer <andreas.kretschmer@schollglas.com>:<br />> am Mon, dem 16.06.2008, um 11:48:01 +1000 mailte novice folgendes:<br /> >> I have a table<br /> >><br/> >> CREATE TABLE problem (<br /> >> problem_id integer,<br /> >> solution_count integer<br/> >> );<br /> >><br /> >> INSERT INTO problem VALUES (1001, 4);<br /> >> INSERT INTOproblem VALUES (1012, 11);<br /> >><br /> >> SELECT * from problem;<br /> >><br /> >> problem_id| solution_count<br /> >> ------------+---------------<br /> >> 1001 | 4<br /> >> 1012 | 11<br /> >> (2 rows)<br /> >><br /> >><br /> >> Is there a way Icould write a query to produce the following? I will<br /> >> need the leading zero for solution < 10<br /> >><br/> >> problem_id | solution<br /> >> -------------+------------<br /> >> 1001 | 01<br/> >> 1001 | 02<br /> ><br /> > My previous answer was a little bit wrong (no leading zero for solution<br/> > < 10), sorry. But no problem:<br /> ><br /> > select problem_id, to_char(generate_Series(1,solution_count),'09')as solution_count from problem ;<br /> ><br /> ><br /> ><br /> >Andreas<br /> > --<br /> > Andreas Kretschmer<br /> > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr:-> Header)<br /> > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA <a href="http://wwwkeys.de.pgp.net">http://wwwkeys.de.pgp.net</a><br/> ><br /> > --<br /> > Sent via pgsql-sql mailinglist (pgsql-sql@postgresql.org)<br /> > To make changes to your subscription:<br /> > <a href="http://www.postgresql.org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</a><br/> ><br /><br /><br/><br /> --<br /> THINK BEFORE YOU PRINT - Save paper if you don't really need to print this.<br /><br /> --<br /> Sentvia pgsql-sql mailing list (pgsql-sql@postgresql.org)<br /> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</a><br/><br /></font>