Thread: Results with leading zero

Results with leading zero

From
novice
Date:
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.


Re: Results with leading zero

From
Andreas Guenzel
Date:
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.
>

Re: Results with leading zero

From
"A. Kretschmer"
Date:
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


Re: Results with leading zero

From
"A. Kretschmer"
Date:
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


Re: Results with leading zero

From
novice
Date:
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.


Re: Results with leading zero

From
"Tena Sakai"
Date:
<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>