How to convert row values of a column to columns - JDBCTemplate and PostgreSQL

Question

I currently have a table:

id | info  | value       | date 
1  | desc  | description | 19-01-1990 10:01:23
2  | lname | Doe         | 19-11-1990 10:01:23
1  | fname | John        | 19-08-1990 10:01:23
1  | dob   | dob         | 19-05-1990 10:01:23
3  | fname | Jo          | 19-01-1990 10:01:23

I would like to query and grab data and do joins with multiple tables later on, so I need it to be:

id | desc        | lname | fname | dob | desc        | date        | ... |
1  | description | Doe   | John  | dob | description | 19-01-1990 10:01:23 | ... |
2  | .........   | ..... | Jo    |     |             | ...         | ... |

I have tried crosstab but it does not seem to work. Any help is appreciated

Answer 1

Your current table is a typical denormalized key value store. You may generate the normalized output you want by aggregating by id and then using max CASE expressions:

SELECT
    id,
    MAX(CASE WHEN info = 'desc'  THEN value END) AS desc,
    MAX(CASE WHEN info = 'lname' THEN value END) AS lname,
    MAX(CASE WHEN info = 'fname' THEN value END) AS fname,
    MAX(CASE WHEN info = 'dob'   THEN value END) AS dob
FROM yourTable
GROUP BY
    id
ORDER BY
    id;

Note that I don't have any column for the date, as you did not give logic for which date value should be retained for each id.

As for the Spring part of your question, you would probably have to use a native query to execute the above.