Columns to rows and export (copy) to a text file

Question

I have a following table

CREATE TABLE usr_addr_part_15
(
  company character varying(70),
  add1 character varying(50),
  add2 character varying(50),
  add3 character varying(50),
  phoneoff character varying(30)
)

with sample data as follows

INSERT INTO usr_addr_part_15
VALUES (
    'A Compnay'
    ,'35/908A4, KINGS ARCADE, NORTH ATLANTA ROAD'
    ,'FL'
    ,'WS-600 025'
    ,'011-200455/255477'
    )
    ,(
    'B Company'
    ,'35/465R, QUEENS ARCADE, WEST ATLANTA ROAD'
    ,'FL'
    ,'WT-601 085'
    ,'0225-2455215/21470277'
    )

So I need to COPY the contents in usr_addr_part_15 to a Text file like below given format :

COMPANY : A Compnay                               
ADD1 : 35/908A4,KINGS ARCADE,NORTH ATLANTA ROAD 
ADD2 : FL                                         
ADD3 : WS-600 025                                 
PHONE(Off.) : 011-200455/255477                   
--- End Of Company 1 ---                          
COMPANY : B Company                               
ADD1 : 35/465R,QUEENS ARCADE,WEST ATLANTA ROAD  
ADD2 : FL                                         
ADD3 : WT-601 085                                 
PHONE(Off.) : 0225-2455215/21470277               
--- End Of Company 2 ---  

On Windows XP, pgAdmin III.

Answer 1

If I understood correctly you can do it like this :

SELECT unnest(format('{COMPANY : %s,ADD1 : %s,ADD2 : %s,ADD3 : %s,PHONE(Off.) : %s,--- End Of Company %s ---}', company, replace(add1, ',', ';'), replace(add2, ',', ';'),  replace(add3, ',', ';'),phoneoff, row_number() OVER ())::TEXT [])
FROM usr_addr_part_15

and copy by querying like

copy(
SELECT unnest(format('{COMPANY : %s,ADD1 : %s,ADD2 : %s,ADD3 : %s,PHONE(Off.) : %s,--- End Of Company %s ---}', company, replace(add1, ',', ';'), replace(add2, ',', ';'),  replace(add3, ',', ';'),phoneoff, row_number() OVER ())::TEXT [])
FROM usr_addr_part_15
)
to 'D:\test.txt' -- the file name that you want save the data