postgresql - transform the content of relational table to the specified form using SQL -

i have database contains following columns:

key arg1 arg2 arg3 timebegin                     timeend #1     b    c    1942-06-18 05:30:00+05:30     1945-06-18 05:30:00+05:30 #2  d    e    f    1940-10-09 05:53:20+05:53:20  1948-10-09 05:53:20+05:53:20 #3  w    x    y     

the type of key, arg1, arg2, arg3 character varying (255) , type of timebegin , timeend timestamp time zone.

now want transform relational table format given below:

<1> <a> <b> <c>                              //key,arg1,arg2,agr3 <2> <d> <e> <f> <3> <w> <x> <y> <1> <a> <b> 1942-06-18 05:30:00+05:30       //with columns containing time attributes key, arg1, arg2, timebegin copied. <1> <a> <b> 1945-06-18 05:30:00+05:30 <1> <d> <e> 1940-10-09 05:53:20+05:53:20 <1> <d> <e> 1948-10-09 05:53:20+05:53:20 

is possible convert relational table format given above using sql. know 1 can dump contents of relational table csv format. possible transform content of relational table specified form given above. database in postgres 9.1

**

*> edit: rules:

1. first rows key, arg1, arg2, arg3 copied , 2. rows containing not null timebegin , timeend values: key,arg1,arg2,timebegin , key,arg1,arg2,timeend copied*

**

note: not matter me whether "<>" placed or not.

the 2 key parts - assuming absolutely must make sql query rather wider post-processing - 1) selecting appropriate columns formatted string in preferred way, , 2) selecting results @ once rather running multiple sql queries.

the first part requires cast data string types, , concatenating them appropriate separators. in postgresql (and sql standard, not other dbmses), string concatenation operator ||. non-standard postgresql syntax typecasts, column::type, although cast(column type) more portable.

for second part, need sql union operator, combines 2 result sets same number , type of columns. there few fiddly bits remember - union remove duplicate rows, can avoided union all; , cannot directly use order by in 2 queries being unioned.

here sample query seems give want. note although appears sort correctly in simple example, think should strictly have order by @ end, after results have been unioned together.

interactive demo here: http://sqlfiddle.com/#!1/7f754/5/0

select string (   select     '<' || key::text || '> '      || '<' || arg1 || '> '     || '<' || arg2 || '> '     || '<' || arg3 || '>' string       foo   order key ) x  union  select string (   select     key,     '<' || key::text || '> '      || '<' || arg1 || '> '     || '<' || arg2 || '> '     || timebegin::text string       foo       timebegin not null     ,     timeend not null    union    select     key,     '<' || key::text || '> '      || '<' || arg1 || '> '     || '<' || arg2 || '> '     || timebegin::text string       foo       timebegin not null     ,     timeend not null    order key ) x