postgresqlselectaggregate-functionsaggregate-filter

Select multiple row values into single row with multi-table clauses

I've searched the forums and while I see similar posts, they only address pieces of the full query I need to formulate (array_aggr, where exists, joins, etc.). If the question I'm posting has been answered, I will gladly accept references to those threads.

I did find this thread ...which is very similar to what I need, except it is for MySQL, and I kept running into errors trying to get it into psql syntax. Hoping someone can help me get everything together. Here's the scenario:

Attribute

attrib_id | attrib_name

UserAttribute

user_id | attrib_id | value

Here's a small example of what the data looks like:

Attribute

attrib_id | attrib_name
-----------------------
1         | attrib1
2         | attrib2
3         | attrib3
4         | attrib4
5         | attrib5

UserAttribute -- there can be up to 15 attrib_id's/value's per user_id

user_id | attrib_id | value
----------------------------
101     | 1         | valueA
101     | 2         | valueB
102     | 1         | valueC
102     | 2         | valueD
103     | 1         | valueA
103     | 2         | valueB
104     | 1         | valueC
104     | 2         | valueD
105     | 1         | valueA
105     | 2         | valueB

Here's what I'm looking for

Result

user_id    | attrib1_value | attrib2_value
--------------------------------------------------------
101        | valueA        | valueB
102        | valueC        | valueD
103        | valueA        | valueB
104        | valueC        | valueD
105        | valueA        | valueB

As shown, I'm looking for single rows that contain: - user_id from the UserAttribute table - attribute values from the UserAttribute table

Note: I only need attribute values from the UserAttribute table for two specific attribute names in the Attribute table

Again, any help or reference to an existing solution would be greatly appreciated.

UPDATE:

@ronin provided a query that gets the results desired:

SELECT ua.user_id
      ,MAX(CASE WHEN a.attrib_name = 'attrib1' THEN ua.value ELSE NULL END) AS attrib_1_val
      ,MAX(CASE WHEN a.attrib_name = 'attrib2' THEN ua.value ELSE NULL END) AS attrib_2_val
  FROM UserAttribute ua
  JOIN Attribute a ON (a.attrib_id = ua.attrib_id)
  WHERE a.attrib_name IN ('attrib1', 'attrib2')
  GROUP BY ua.user_id;

To build on that, I tried to add some 'LIKE' pattern matching within the 'WHEN' condition (against the ua.value), but everything ends up as the 'FALSE' value. Will start a new question to see if that can be incorporated if I cannot figure it out. Thanks all for the help!!

Solution

  • If each attribute only has a single value for a user, you can start by making a sparse matrix:

    SELECT user_id
      ,CASE WHEN attrib_id = 1 THEN value ELSE NULL END AS attrib_1_val
      ,CASE WHEN attrib_id = 2 THEN value ELSE NULL END AS attrib_2_val
  FROM UserAttribute;

    Then compress the matrix using an aggregate function:

    SELECT user_id
      ,MAX(CASE WHEN attrib_id = 1 THEN value ELSE NULL END) AS attrib_1_val
      ,MAX(CASE WHEN attrib_id = 2 THEN value ELSE NULL END) AS attrib_2_val
  FROM UserAttribute
  GROUP BY user_id;

    In response to the comment, searching by attribute name rather than id:

    SELECT ua.user_id
      ,MAX(CASE WHEN a.attrib_name = 'attrib1' THEN ua.value ELSE NULL END) AS attrib_1_val
      ,MAX(CASE WHEN a.attrib_name = 'attrib2' THEN ua.value ELSE NULL END) AS attrib_2_val
  FROM UserAttribute ua
  JOIN Attribute a ON (a.attrib_id = ua.attrib_id)
  WHERE a.attrib_name IN ('attrib1', 'attrib2')
  GROUP BY ua.user_id;