sql - 列到行并导出(复制)到文本文件

Question

我有下表

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)
)

示例数据如下

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'
    )

所以我需要将 usr_addr_part_15 中的内容COPY 到文本文件，如下给定格式:

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 ---  

在 Windows XP 上，pgAdmin III。

Answer 1

如果我没理解错，你可以这样做:

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

并通过查询复制

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