Revision history [back]

One way to transform duplicate rows in SQL into distinct columns and then merge them together is by using aggregate functions and the GROUP BY clause. The steps to achieve this could be:

Use the SELECT statement to select the columns that contain duplicate data.
Apply an aggregate function like MAX, MIN, COUNT, SUM, or AVG to each column that contains duplicates. This will group the duplicate values together into a single row for each unique value in another column.
Use the GROUP BY clause to group the data by the column that contains unique values. This will create a new row for each unique value in that column.
Use the JOIN or UNION operator to combine the grouped data into a single table.

Here's an example of transforming duplicate rows in a table called "orders" that has columns for "customername", "productname", and "quantity" into distinct columns and then merging them together:

SELECT customername, MAX(CASE WHEN productname = 'Product A' THEN quantity END) AS ProductAQuantity, MAX(CASE WHEN productname = 'Product B' THEN quantity END) AS ProductBQuantity, MAX(CASE WHEN productname = 'Product C' THEN quantity END) AS ProductCQuantity FROM orders GROUP BY customer_name;

This SQL statement would create a table with columns for "customername", "ProductAQuantity", "ProductBQuantity", and "ProductC_Quantity" where each row represents a unique customer and their corresponding quantities for each product.