Re: Converting Rows to Columns

Joshua Berry <yoberi(at)gmail(dot)com> 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 '')
>
> Note that the order of the sellers does not matter.
>
> Any tips? I've googled 'sql convert rows to columns' and got some results that
> appear to be mssql specific. On Stackoverflow there is an example of how to
> convert columns to rows using UNION, but not visa versa.

based on my other answer:

test=*# select * from (
select
id,
coalesce((array_accum(name))[1],'---') as name1,
coalesce((array_accum(name))[2],'---') as name2,
coalesce((array_accum(name))[3],'---') as name3
from joshua
group by id
order by id) foo;
id | name1 | name2 | name3
----+-------+-------+-------
1 | user1 | user2 | user3
2 | user4 | user5 | ---
3 | user6 | --- | ---
(3 rows)

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°