Re: Converting Rows to Columns

Поиск
Список
Период
Сортировка
От Chris Spotts
Тема Re: Converting Rows to Columns
Дата
Msg-id 58B2DF77289A4DA08685DD5D6F2AA556@tcore.com
обсуждение исходный текст
Ответ на Converting Rows to Columns  (Joshua Berry <yoberi@gmail.com>)
Список pgsql-general
Дерево обсуждения

It sounds like you want a crosstab query.  There is probably (I don’t know what version of postgres you’re using) a contrib package called “tablefunc” that includes the crosstab functions you’re looking for.

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Joshua Berry
Sent: Tuesday, May 05, 2009 9:39 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Converting Rows to Columns

 

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.

 

Thanks in advance!

 

Joshua

 

Joshua Berry

Software Engineer

Opentech, S.A.

+(595 21) 282557 Work

+(595) 981 330 701 Mobile

 

В списке pgsql-general по дате отправления:

Предыдущее
От: "Markus Wollny"
Дата:
Сообщение: PL/Perl: Is there a way to use spi_prepare/spi_exec_prepared with a list as second argument?
Следующее
От: Dennis Brakhane
Дата:
Сообщение: Re: PGSQL-to-MYSQL Migration: Error in a 'simple' inner join query