PostgreSQL行转列：多条件查询无匹配返回NULL的技术问询

Alright, so you need to turn that attribute-heavy long table into a clean wide table where each ad_id gets a single row, with columns for every possible name value—filling in NULL whenever there’s no matching data for an attribute. Here are two straightforward, practical ways to pull this off in PostgreSQL:

Method 1: Using CASE WHEN (Universal SQL Approach) #

This method works across nearly all SQL databases, so it’s great if you need portability. We’ll use MAX() to aggregate the valueofname for each attribute (since each ad_id + name pair should be unique; if not, swap MAX() for something like STRING_AGG to combine multiple values).

Query for a Specific ad_id #

SELECT
  ad_id,
  MAX(CASE WHEN name = 'name' THEN valueofname END) AS name,
  MAX(CASE WHEN name = 'age' THEN valueofname END) AS age,
  MAX(CASE WHEN name = 'birthday' THEN valueofname END) AS birthday,
  MAX(CASE WHEN name = 'job' THEN valueofname END) AS job
FROM your_table_name
WHERE ad_id = 1 -- Replace with your target ad_id
GROUP BY ad_id;

Query for All ad_ids #

Just drop the WHERE clause if you want to pivot every record in the table:

SELECT
  ad_id,
  MAX(CASE WHEN name = 'name' THEN valueofname END) AS name,
  MAX(CASE WHEN name = 'age' THEN valueofname END) AS age,
  MAX(CASE WHEN name = 'birthday' THEN valueofname END) AS birthday,
  MAX(CASE WHEN name = 'job' THEN valueofname END) AS job
FROM your_table_name
GROUP BY ad_id
ORDER BY ad_id;

Method 2: Using PostgreSQL's FILTER Clause (Cleaner Syntax) #

PostgreSQL has a handy FILTER clause for aggregates that makes the query more readable and concise. It does the exact same job as the CASE WHEN approach but with cleaner, more intentional code:

Specific ad_id Query #

SELECT
  ad_id,
  MAX(valueofname) FILTER (WHERE name = 'name') AS name,
  MAX(valueofname) FILTER (WHERE name = 'age') AS age,
  MAX(valueofname) FILTER (WHERE name = 'birthday') AS birthday,
  MAX(valueofname) FILTER (WHERE name = 'job') AS job
FROM your_table_name
WHERE ad_id = 2 -- Target ad_id here
GROUP BY ad_id;

All ad_ids Query #

SELECT
  ad_id,
  MAX(valueofname) FILTER (WHERE name = 'name') AS name,
  MAX(valueofname) FILTER (WHERE name = 'age') AS age,
  MAX(valueofname) FILTER (WHERE name = 'birthday') AS birthday,
  MAX(valueofname) FILTER (WHERE name = 'job') AS job
FROM your_table_name
GROUP BY ad_id
ORDER BY ad_id;

Test with Your Sample Data #

First, let’s set up your sample data to verify the queries work:

CREATE TABLE your_table_name (
  ad_id INT,
  name VARCHAR(50),
  valueofname VARCHAR(50)
);

INSERT INTO your_table_name VALUES
(1, 'name', 'brian'),
(1, 'age', '23'),
(2, 'job', 'IT'),
(2, 'name', 'Jack');

Running the all-ad_ids query will return exactly the wide table you want:

Quick Notes #

• If you have more name values (like address or email), just add additional columns using the same pattern.
• If an ad_id has multiple entries for the same name, MAX() will pick one—use STRING_AGG(valueofname, ',') instead if you want to combine all matching values into a single string.

内容的提问来源于stack exchange，提问作者dev_medo