pivot table example

gistfile1.sql

CREATE TABLE user_account (
   id SERIAL
);

CREATE TABLE id_code (
   user_account_id BIGINT,
   code_type TEXT,
   value TEXT
);

INSERT INTO user_account DEFAULT VALUES;
INSERT INTO id_code (user_account_id, code_type, value) VALUES (1,'ssn','12345689');
INSERT INTO id_code (user_account_id, code_type, value) VALUES (1,'account-number','55555');
INSERT INTO id_code (user_account_id, code_type, value) VALUES (1,'internal-code','foryoureyesonly');

SELECT acc.id,
        max(CASE WHEN (idc.code_type = E'ssn') THEN idc.value END) AS ssn,
        max(CASE WHEN (idc.code_type = E'account-number') THEN idc.value END) AS user_account_number,
        max(CASE WHEN (idc.code_type = E'internal-code') THEN idc.value END) AS internal_id_code
   FROM user_account AS acc
   INNER JOIN id_code AS idc
     ON (idc.user_account_id = acc.id)
   WHERE (acc.id = 1)
   GROUP BY acc.id;

  id |   ssn    | user_account_number | internal_id_code
----+----------+---------------------+------------------
   1 | 12345689 | 55555               | foryoureyesonly
(1 row)