2

Is it possible to transpose a table with repeated columns?

Existing table:

user_id    question_id   body 
1          1             'Text1 1'
1          1             'Text1 1-2'
1          2             'Text1 2'
1          3             'Text1 3'
2          1             'Text2 1'
2          2             'Text2 2'

Cross tab or solution based on

MAX(CASE WHEN r.question_id = 1 THEN r.body ELSE NULL END) AS 'question1'

is not applicable in this scenario because always is match last occurance of repeated attribute.

I want to perform search on the question body but I don't know how without a transposed table.
E.g. I want to find user_id where question1='...' AND question2='...'

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
John Mahn
  • 23
  • 2

1 Answers1

1

This is a case of relational division. Two example query techniques to get what you want:

SELECT user_id
FROM   tbl
WHERE  question_id = 1
AND    body = 'Text1 1'

INTERSECT
SELECT user_id
FROM   tbl
WHERE  question_id = 2
AND    body = 'Text1 2';

Or:

SELECT t1.user_id
FROM   tbl t1
JOIN   tbl t2 USING (user_id)
WHERE  t1.question_id = 1
AND    t1.body = 'Text1 1'
AND    t2.question_id = 2
AND    t2.body = 'Text1 2';

-> sqlfiddle demo

Find many more under this related question:
How to filter SQL results in a has-many-through relation

crosstab() from the additional module tablefunc is just a way to display your data in a modified way. But you are looking for a query technique. It's not a display issue.
You can find many examples for crosstab() here on SO, too, with a search like this one.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks this is what I am looking for. I must find out which technique is less expensive to execute (search based on 1 .. 10 attributes) Technique "MAX (CASE when ... " is good for me if for "group by" entity (here it is user_id) exist max one occurrence of attribute (attribute_id). So at first you transpose table and than you can perform search. – John Mahn Dec 20 '12 at 11:16
  • I don't know if it is good design.. I read some articles about that and this kind of problem is called "entity attribute value" model.. You can extend easily model with another question but it has bad impact on search.. Do you have some material on this subject ? Something like "Tuning\Optimialization E-A-V model" etc.. ? I have really bad experiences with solving this kind of problems.. – John Mahn Dec 20 '12 at 11:17
  • @JohnMahn: When dealing with EAV, you may be interested in the [hstore](http://www.postgresql.org/docs/current/interactive/hstore.html) module. Try a search for [EAV here on SO](http://stackoverflow.com/questions/tagged/entity-attribute-value), or for a couple of [very interesting answers on dba.SE](http://dba.stackexchange.com/questions/tagged/eav). – Erwin Brandstetter Dec 20 '12 at 11:23