How to eliminate duplication of the same lines
I have a table in my database that looks like (the table may have the same tuples):
+-------------+---------+--------+
| ProductName | Status | Branch |
+-------------+---------+--------+
| P1 | dead | 1 |
| P1 | dead | 2 |
| P1 | dead | 2 |
| P2 | expired | 1 |
+-------------+---------+--------+
I want to show the result after (the Branch attribute is dynamic):
+-------------+---------+--------+
| ProductName | Branch 1|Branch 2|
+-------------+---------+--------+
| P1 | dead | dead|
| P2 | expired | OK |
+-------------+---------+--------+
After some help, I came up with the following solution:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'GROUP_CONCAT(case when branch = ''',
branch,
''' then status ELSE NULL end) AS ',
CONCAT('Branch',branch)
)
) INTO @sql
FROM Table1;
SET @sql = CONCAT('SELECT productName, ', @sql, '
FROM Table1
GROUP BY productName');
PREPARE stmt FROM @sql;
EXECUTE stmt;
The result is shown as:
+-------------+---------+-----------+
| productName | Branch1 | Branch2 |
+-------------+---------+-----------+
| p1 | dead | dead,dead |
| p2 | expired | (null) |
+-------------+---------+-----------+
SQL Fiddle .
Now I want to show the status of the product as "OK" and not null when the status is null, and also don't need to concatenate the status if there is a duplicate product in the table. Tried a lot but couldn't figure it out. Thanks in advance.
+3
source to share
1 answer
It's a little tricky because there will be repetition if the statuses are the same, so the union should be outside of GROUP_CONCAT
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'COALESCE(GROUP_CONCAT(DISTINCT case when branch = ''',
branch,
''' then status end),''OK'') AS ',
CONCAT('Branch',branch)
)
) INTO @sql
FROM Table1;
SET @sql = CONCAT('SELECT productName, ', @sql, '
FROM Table1
GROUP BY productName');
PREPARE stmt FROM @sql;
EXECUTE stmt;
+2
source to share