Postgresql – How to partially merge a row in Postgres

postgresql

I have 3 tables:

Customers:      Items:    Purchases:
  id              id        id
  first_name      name      customer_id
  last_name       price     item_id

I need to present data in a way that a can see all the purchases of a customer, per item, the amount (sum) of each item and the total price of this amount(again, per item), like this:

Name:   | Last Name:  | Item:     | Price:  | Amount: | Total:
John    | Doe         | Pencil    | 0.5     | 5       | 2.5
John    | Doe         | Paper(A4) | 3.0     | 20      | 60.00
Michael | Smith       | Pencil    | 0.5     | 3       | 1.5
Michael | Smith       | Paper(A4) | 3.0     | 10      | 30.00

I manage to do that with this query:

SELECT
  customers.first_name AS "Name",
  customers.last_name AS "Last Name",
  items.name AS "Item",
  items.price AS "Price",
  COUNT(items.id) AS "Amount",
  SUM(items.price) AS "Total"
FROM
  items
INNER JOIN
  purchases ON items.id = purchases.item_id
INNER JOIN
    customers ON purchases.customer_id = customers.id
GROUP BY
  customers.id, items.id
ORDER BY customers.last_name

Is there a way to do it like you would in Excel, for example, where you can merger those cells that have the first and last name, so the customer name is listed only once? I now that it is not the most relevant "problem", but I have to do this twice a week in a meeting with 5 other people and it would be great to improve this layout.

Best Answer

You can simple use lag window function to get the value of the previous row and compare with the current one, then you use CASE to conditionally format as you please. Mapping the "merged" values as NULL (so it can work for any type), it would be:

SELECT
  CASE
  WHEN customers.id = lag(customers.id) OVER w_ordered THEN
    NULL
  ELSE
    customers.first_name
  END AS "Name",
  CASE
  WHEN customers.id = lag(customers.id) OVER w_ordered THEN
    NULL
  ELSE
    customers.last_name
  END AS "Last Name",
  items.name AS "Item",
  items.price AS "Price",
  COUNT(items.id) AS "Amount",
  SUM(items.price) AS "Total"
FROM
  items
INNER JOIN
  purchases ON items.id = purchases.item_id
INNER JOIN
    customers ON purchases.customer_id = customers.id
GROUP BY
  customers.id, items.id
WINDOW
  w_ordered AS (ORDER BY customers.last_name)
ORDER BY customers.last_name

Notice that I used the same ORDER BY clause in the window function as the full query, this is really important because you do want to verify the "merging" following the same rule.

Now, this is not a presentation mode, as in SQL you only have a table as a result, if you wish to do a real row merging in your presentation layer, you can use another column to just inform if it should merge the rows or not (although it shouldn't be hard to do the full logic there).

Related Question