[SQL] 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Results with leading zero
Am Mon, 16 Jun 2008 11:48:01 +1000 schrieb novice <[EMAIL PROTECTED]>: 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. > signature.asc Description: PGP signature
Re: [SQL] Results with leading zero
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Results with leading zero
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
Re: [SQL] Results with leading zero
Perfect! Thank you very mcuh :) 2008/6/16 A. Kretschmer <[EMAIL PROTECTED]>: > 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
