Search Postgresql Archives

Re: Converting Rows to Columns

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Joshua Berry <yoberi@xxxxxxxxx> wrote:

> Greetings all,
> 
> I know that this is an elementary question, so I'm just asking for a pointer in
> the right direction.
> 
> I have a query like this that tries to link Retail shops (from the table
> aliased as 'a') to sales reps (from the table aliased as 'e') that service the
> given Retail shop:
> 
> select a.id_pdv, e.apellido ||', ' || e.nombre as seller_name from tbl_pdvs a
> join tbl_circuitos b using (id_circuito) join tbl_frecuencias c on
> b.id_circuito = c.id_circuito join tbl_rutas d on c.id_ruta = d.id_ruta join
> tbl_vendedores e on d.vendedor = e.id_vendedor order by a.id_pdv limit 4;
> 
> 
>  id_pdv  |   seller_name           
> ---------+-------------------
>  1000001 | COLON, CRISTOBOL
>  1000001 | LOPEZ, CARLOS
>  1000002 | COLON, CRISTOBOL
>  1000002 | LUGO, FERNANDO
> 
> 
> As you can see, there are two salesmen that service this shop. 
> 
> What I need is to be able to create a report that has each unique id_pdv as a
> row of the result set, with three columns available to show up to three salemen
> that are assigned to the route.
> 
> So this would give something like this:
> 
>  id_pdv  |  seller_name1    | seller_name2   | seller_name3              
> ---------+------------------+----------------+----------------
>  1000001 | COLON, CRISTOBOL | LOPEZ, CARLOS  | (\N or '')
>  1000002 | COLON, CRISTOBOL | LUGO, FERNANDO | (\N or '')

Not really your desired result, but i think, a solution:

test=# select * from joshua ;
 id | name
----+-------
  1 | user1
  1 | user2
  1 | user3
  2 | user4
  2 | user5
  3 | user6
(6 rows)

Time: 0.138 ms
test=*# CREATE AGGREGATE array_accum (anyelement)
test-# (
test(#     sfunc = array_append,
test(#     stype = anyarray,
test(#     initcond = '{}'
test(# );
CREATE AGGREGATE
Time: 0.503 ms
test=*# select id, array_accum(name) from joshua group by id order by
id;
 id |     array_accum
----+---------------------
  1 | {user1,user2,user3}
  2 | {user4,user5}
  3 | {user6}
(3 rows)

Time: 0.369 ms
test=*#





Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux