Query issue (row values to columns) in PostgreSQL

Question

I've been looking around this, but I can not go forward and is stopping a project that I'm into. My issue (I think) is very simple, but because I'm not familiar with postgresql I'm stuck in page 1.

I've this table:

"id";"date";"name";"seller";"order";"result"
"1";"2013-12-10 11:09:28.76";"adam";"mu";1;"5"
"1";"2013-12-10 11:09:28.76";"adam";"mu";2;"3"
"1";"2013-12-10 11:09:28.76";"adam";"mu";3;"1"
"2";"2013-12-10 11:10:26.059";"eve";"wa";1;"3"
"2";"2013-12-10 11:10:26.059";"eve";"wa";2;"9"
"2";"2013-12-10 11:10:26.059";"eve";"wa";3;"5"
"3";"2013-12-10 11:11:34.746";"joshua";"mu";1;"2"
"3";"2013-12-10 11:11:34.746";"joshua";"mu";2;"2"
"3";"2013-12-10 11:11:34.746";"joshua";"mu";3;"9"

Creation script:

CREATE TABLE myTable
(
  id character varying(50) NOT NULL,
  date timestamp without time zone NOT NULL,
  name character varying(64) NOT NULL,
  seller character varying(64) NOT NULL,
  order integer NOT NULL,
  result character varying(64)
)
WITH (OIDS=FALSE);
ALTER TABLE myTable OWNER TO postgres;

(Note: I can not modify the structure of that table)

And I want to get a result like this for use the copy function and write it to file:

"id";"date";"name";"seller";"result_1";"result_2";"result_3"
"1";"2013-12-10 11:09:28.76";"adam";"mu";"5";"3";"1"
"2";"2013-12-10 11:10:26.059";"eve";"wa";"3";"9";"5"
"3";"2013-12-10 11:11:34.746";"joshua";"mu";"2";"2";"9"

I've looked into the "crosstab" function, but I can not get that work within my environment and also I want to lose the column order in my output.

I'm not a query expert so I'm very over my head here :(

Any help will be appreciated. Thanks in advance!

Postgres *never* uses double quotes for *values*, only for identifiers (if needed). — Erwin Brandstetter, Dec 16 '13 at 21:47
Yes, I know, but thats was the fastest way to get my post published. I'm not so very familiar with the StackOverflow interfase for writing posts. — Luke, Dec 16 '13 at 21:50

score 2 · Accepted Answer · edited May 23 '17 at 11:52

2

CASE statements are the poor man's surrogate for a proper crosstab() function:

SELECT a.id
      ,max(CASE WHEN myorder = 1 THEN result END) AS result_1
      ,max(CASE WHEN myorder = 2 THEN result END) AS result_2
      ,max(CASE WHEN myorder = 3 THEN result END) AS result_3
FROM   mytab
GROUP  BY id
ORDER  BY id;

Only requires a single table scan and is therefore much faster than multiple joins.

BTW, never use reserved words like order as identifiers.

Details for this as well as a proper crosstab() query under this related question:
PostgreSQL Crosstab Query

edited May 23 '17 at 11:52

Community

1
1

answered Dec 16 '13 at 22:01

Erwin Brandstetter

605,456
145
1,078
1,228

This is the best solution so far! Thank you very much! – Luke Dec 16 '13 at 23:03

Sam · Answer 2 · 2013-12-16T21:29:02.527

1

This is not exactly what you want but it creates an array for results field for each id. Similar to group_concat in MySQL

 SELECT id, array_agg(result)
 FROM table
 GROUP BY id

edited Dec 16 '13 at 21:29

answered Dec 16 '13 at 21:18

Sam

2,761
3
19
30

1

`array_agg` does *not* create a "comma-separated" list. It creates an `array`. If you want a delimited list, use `string_agg` – Dec 16 '13 at 21:27
Thanks to both. That's not what I'm looking for as a final result, but maybe I can try it. Thanks! – Luke Dec 16 '13 at 21:48

score 1 · Answer 3 · answered Dec 16 '13 at 21:34

1

If you can't use crosstab (tablefunc module isn't installled?) How about something like this:

SELECT a.id,  b.myresult as one, c.myresult as two, d.myresult as three
FROM (SELECT id
      FROM mytab
      GROUP BY id) a
JOIN mytab b ON a.id = b.id AND b.myorder = 1
JOIN mytab c ON a.id = c.id AND c.myorder = 2
JOIN mytab d ON a.id = d.id AND d.myorder = 3

http://www.sqlfiddle.com/#!15/a3780/6

answered Dec 16 '13 at 21:34

Barbara Laird

12,599
2
44
57

Hi, thanks for your answer! That was my first approach, but I got stuck when adding the other columns into the select from the table "mytab". On the pgsql I get this in the 'group by': "ERROR: column xxxxx must appear in the GROUP BY clause or be used in an aggregate function" – Luke Dec 16 '13 at 21:47
Is the data in the other columns different for each row? http://www.sqlfiddle.com/#!15/c2b88/1 Since the other data now matters, you might want to add the expected input and output to your question. – Barbara Laird Dec 16 '13 at 21:54
Hi, thanks for your reply! I've edited my original post per your request, thanks in advance! – Luke Dec 16 '13 at 22:06
http://www.sqlfiddle.com/#!15/97f0c/1, adding it to the inner group by would work – Barbara Laird Dec 16 '13 at 22:09
I really apreciate your help! I think I'm getting close to the output requested. I've edited the OP so you can see the real structure of my table. I didn't knew that one column more or less can differ so much the output. Thanks! – Luke Dec 16 '13 at 22:22

Query issue (row values to columns) in PostgreSQL

3 Answers3