Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #362 > unrolled thread

(sql beginner) help for request

Back to article view | Back to comp.databases.postgresql

Contents

  (sql beginner) help for request "meta" <xyz@abc.fr> - 2012-06-20 21:56 +0200
    Re: (sql beginner) help for request Stuart McGraw <smcg4191@frii.com> - 2012-06-20 16:09 -0600
      Re: (sql beginner) help for request Stuart McGraw <smcg4191@frii.com> - 2012-06-20 16:31 -0600
    Re: (sql beginner) help for request Harry Tuttle <OTPXDAJCSJVU@spammotel.com> - 2012-06-21 08:44 +0200
    Re: (sql beginner) help for request Jasen Betts <jasen@xnet.co.nz> - 2012-06-21 07:59 +0000
    Re: (sql beginner) help for request "meta" <xyz@abc.fr> - 2012-06-22 23:46 +0200

#362 — (sql beginner) help for request

Hi

I know postgresql but not enough to solve the following problem:

Here is a table with the fields: name char(20), c1 char(1), c2 char (1)

The goal is to concat these fields after transformation in order to display 
them on one ligne:
- c1 and c2 values are Y or N. If 'Y' it will be replaced by 'A', if 'N' it 
will be replaced by 'B'.
- name must have 20 characters even if its lenght is less than 20, in order 
to align the results properly

For example, here are two records: ('apple','Y','N') and 
('cranberry','N','N')

Final display wished:
apple                  A B
cranberry            B B

But I don't know how in the same request to concat fields, add a number of 
spaces at the end of name depending of its length and replace one value by 
another one...

Thanks for any help.

[toc] | [next] | [standalone]

#363

On 06/20/2012 01:56 PM, meta wrote:
> Hi
> 
> I know postgresql but not enough to solve the following problem:
> 
> Here is a table with the fields: name char(20), c1 char(1), c2 char (1)
> 
> The goal is to concat these fields after transformation in order to display 
> them on one ligne:
> - c1 and c2 values are Y or N. If 'Y' it will be replaced by 'A', if 'N' it 
> will be replaced by 'B'.
> - name must have 20 characters even if its lenght is less than 20, in order 
> to align the results properly
> 
> For example, here are two records: ('apple','Y','N') and 
> ('cranberry','N','N')
> 
> Final display wished:
> apple                  A B
> cranberry            B B
> 
> But I don't know how in the same request to concat fields, add a number of 
> spaces at the end of name depending of its length and replace one value by 
> another one...
> 
> Thanks for any help.

How about:

create table test (fruit text, x char(1), y char(1));
insert into test values ('apple','Y','N'),('cranberry', 'N','N'); 

select * from test;
   fruit   | x | y 
-----------+---+---
 apple     | Y | N
 cranberry | N | N

select fruit::char(20), translate(x,'YN','AB') as x1, translate(y,'YN','AB') as y1 from test;

        fruit         | x1 | y1 
----------------------+----+----
 apple                | A  | B
 cranberry            | B  | B

[toc] | [prev] | [next] | [standalone]

#364

On 06/20/2012 04:09 PM, Stuart McGraw wrote:
> On 06/20/2012 01:56 PM, meta wrote:
>> Hi
>> 
>> I know postgresql but not enough to solve the following problem:
>> 
>> Here is a table with the fields: name char(20), c1 char(1), c2 char (1)
>> 
>> The goal is to concat these fields after transformation in order to display 
>> them on one ligne:
>> - c1 and c2 values are Y or N. If 'Y' it will be replaced by 'A', if 'N' it 
>> will be replaced by 'B'.
>> - name must have 20 characters even if its lenght is less than 20, in order 
>> to align the results properly
>> 
>> For example, here are two records: ('apple','Y','N') and 
>> ('cranberry','N','N')
>> 
>> Final display wished:
>> apple                  A B
>> cranberry            B B
>> 
>> But I don't know how in the same request to concat fields, add a number of 
>> spaces at the end of name depending of its length and replace one value by 
>> another one...
>> 
>> Thanks for any help.
> 
> How about:
...snip...

Sorry, read your post too quickly, please ignore my 
previous reply...

create table test (name char(20), c1 char(1), c2 char(1));
insert into test values ('apple','Y','N'),('cranberry', 'N','N'); 

test=# select * from test;
         name         | c1 | c2 
----------------------+----+----
 apple                | Y  | N
 cranberry            | N  | N

select rpad(name,20) || translate(c1,'YN','AB') || ' ' || translate(c2,'YN','AB') as c from test;

            c            
-------------------------
 apple               A B
 cranberry           B B

[toc] | [prev] | [next] | [standalone]

#365

meta, 20.06.2012 21:56:
> Hi
>
> I know postgresql but not enough to solve the following problem:
>
> Here is a table with the fields: name char(20), c1 char(1), c2 char (1)
>
> The goal is to concat these fields after transformation in order to display them on one ligne:
> - c1 and c2 values are Y or N. If 'Y' it will be replaced by 'A', if 'N' it will be replaced by 'B'.
> - name must have 20 characters even if its lenght is less than 20, in order to align the results properly
>
> For example, here are two records: ('apple','Y','N') and ('cranberry','N','N')
>
> Final display wished:
> apple                  A B
> cranberry            B B
>
> But I don't know how in the same request to concat fields, add a number of spaces at the end of name depending of its length and replace one value by another one...
>
> Thanks for any help.
>

select rpad(name, 20, ' ')||
        case when c1 = 'Y' then 'A' else 'B' end||
        case when c2 = 'Y' then 'A' else 'B' end
from the_table

[toc] | [prev] | [next] | [standalone]

#366

On 2012-06-20, meta <xyz@abc.fr> wrote:
> Hi
>
> I know postgresql but not enough to solve the following problem:
>
> Here is a table with the fields: name char(20), c1 char(1), c2 char (1)
>
> The goal is to concat these fields after transformation in order to display 
> them on one ligne:
> - c1 and c2 values are Y or N. If 'Y' it will be replaced by 'A', if 'N' it 
> will be replaced by 'B'.
> - name must have 20 characters even if its lenght is less than 20, in order 
> to align the results properly
>
> For example, here are two records: ('apple','Y','N') and 
> ('cranberry','N','N')
>
> Final display wished:
> apple                  A B
> cranberry            B B

postgres isn't capable of guessing the behaviour of variable 
pitch (proportional) fonts like you appear to want with your
example output.

assuming you want it for fixed pich font like this instead:

apple                A B
cranberry            B B

select rpad(name,20) ||' '||translate(c1||' '||c2,'YN','AB');

-- 
⚂⚃ 100% natural

[toc] | [prev] | [next] | [standalone]

#367

Thanks to everybody, the given solutions ran ok.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web