1

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!

Improve this question
2
  • 1
    Postgres never uses double quotes for values, only for identifiers (if needed). Commented Dec 16, 2013 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. Commented Dec 16, 2013 at 21:50

3 Answers 3

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is the best solution so far! Thank you very much!
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
Improve this answer

2 Comments

array_agg does not create a "comma-separated" list. It creates an array. If you want a delimited list, use string_agg
Thanks to both. That's not what I'm looking for as a final result, but maybe I can try it. Thanks!
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

Improve this answer

5 Comments

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"
Is the data in the other columns different for each row? sqlfiddle.com/#!15/c2b88/1 Since the other data now matters, you might want to add the expected input and output to your question.
Hi, thanks for your reply! I've edited my original post per your request, thanks in advance!
sqlfiddle.com/#!15/97f0c/1, adding it to the inner group by would work
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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.