Thread: Converting Rows to Columns

2009/5/5 Joshua Berry <yoberi@gmail.com>:
> 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.

Quick and dirty solution off the top of my head for the problem described:

CREATE TABLE salesperson (
  id SERIAL PRIMARY KEY,
  name TEXT
);

CREATE TABLE salesperson_store (
  salesperson_id INT NOT NULL,
  store_id INT NOT NULL,
  PRIMARY KEY (salesperson_id, store_id)
);

INSERT INTO salesperson VALUES
  (1, 'COLON, CRISTOBOL'),
  (2, 'OPEZ, CARLOS'),
  (3, 'LUGO, FERNANDO');

INSERT INTO salesperson_store VALUES
  (1, 1000001),
  (1, 1000002),
  (2, 1000001),
  (3, 1000002);

SELECT store_id,
       (SELECT sp.name FROM salesperson sp
          INNER JOIN salesperson_store sps
           ON sp.id=sps.salesperson_id
           AND sps.store_id=store.store_id
        ORDER BY sp.name LIMIT 1 OFFSET 0)
        AS seller_name1,
         (SELECT sp.name FROM salesperson sp
          INNER JOIN salesperson_store sps
           ON sp.id=sps.salesperson_id
           AND sps.store_id=store.store_id
          ORDER BY sp.name LIMIT 1 OFFSET 1)
        AS seller_name2,
       (SELECT sp.name FROM salesperson sp
          INNER JOIN salesperson_store sps
           ON sp.id=sps.salesperson_id
           AND sps.store_id=store.store_id
           ORDER BY sp.name LIMIT 1 OFFSET 2)
        AS seller_name3
  FROM (SELECT DISTINCT(store_id) FROM salesperson_store) store
ORDER BY store_id;

 store_id |   seller_name1   |  seller_name2  | seller_name3
----------+------------------+----------------+--------------
  1000001 | COLON, CRISTOBOL | OPEZ, CARLOS   |
  1000002 | COLON, CRISTOBOL | LUGO, FERNANDO |
(2 rows)

(No doubt there are probably more elegant ways of doing this)

HTH

Ian Barwick

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Joshua Berry
> Sent: Tuesday, May 05, 2009 10: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:
> [snip]

Is a crosstab query what you're looking for?

Mike

Joshua Berry <yoberi@gmail.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°

Joshua Berry <yoberi@gmail.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 '')

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°