# 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).