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

Groups > comp.databases.postgresql > #364

Re: (sql beginner) help for request

Show all headers | View raw

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

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

(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

csiph-web