Converting Rows to Columns

-----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

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

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�

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�

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