4

This is the example

banzai=# select letter_id, length_id, word from words;
 letter_id | length_id | word  
-----------+-----------+-------
         1 |         1 | run
         3 |         1 | tea
         2 |         1 | cat
         2 |         2 | cast
         2 |         3 | coast
         1 |         3 | roast
         1 |         2 | rest
         3 |         2 | team
         3 |         3 | toast
(9 rows)

banzai=# select letter from letters;
 letter 
--------
 R
 C
 T
(3 rows)


banzai=# select length from lengths;
 length 
--------
      4
      5
      3
(3 rows)

banzai=# select length, letter, word from words, lengths, letters where words.length_id = lengths.id and words.letter_id = letters.id;
 length | letter | word  
--------+--------+-------
      3 | C      | cat
      3 | R      | run
      3 | T      | tea
      4 | R      | rest
      4 | C      | cast
      4 | T      | team
      5 | R      | roast
      5 | C      | coast
      5 | T      | toast
(9 rows)

I want to produce the following table in HTML

       R       T       C
3     run     tea      cat
4     rest    team     cast
5     roast   toast    coast

I have a method in my java (backend) code that will produce the data in json. Angularjs (frontend) will take the json and present the table in html

Improve this question
1
  • search for dynamic pivot Commented Mar 2, 2015 at 23:07

1 Answer 1

Reset to default
3

As you want JSON this will return a single object:

select json_object_agg(length, o)
from (
    select length, json_object_agg(letter, word) as o
    from
        words w
        inner join
        lengths l on w.length_id = l.id
        inner join
        letters t on w.letter_id = t.id
    group by length
) s;
                                                                        json_object_agg                                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 { "4" : { "R" : "rest", "C" : "cast", "T" : "team" }, "5" : { "R" : "roast", "C" : "coast", "T" : "toast" }, "3" : { "C" : "cat", "R" : "run", "T" : "tea" } }

The above query is for 9.4. In 9.3 it is a bit more difficult but it can be done as well.

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

Comments

Your Answer

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