CODEWITHSUNDEEP
mysql
Hasnain
Hasnain

Reputation: 99

How to eliminate the repetition of same rows

I have a table in my database which looks like (there can be same tuples in table):

+-------------+---------+--------+
| ProductName | Status  | Branch |
+-------------+---------+--------+
| P1          | dead    |      1 |
| P1          | dead    |      2 |
| P1          | dead    |      2 |
| P2          | expired |      1 |
+-------------+---------+--------+

I want to show the result after as (the Branch attribute is dynamic):

+-------------+---------+--------+
| ProductName | Branch 1|Branch 2|
+-------------+---------+--------+
| P1          | dead    |    dead|
| P2          | expired |     OK |
+-------------+---------+--------+

After some help i came up with 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 shown is like:

+-------------+---------+-----------+
| productName | Branch1 |  Branch2  |
+-------------+---------+-----------+
| p1          | dead    | dead,dead |
| p2          | expired | (null)    |
+-------------+---------+-----------+

SQL Fiddle.
What i want now is to show product status as "OK" instead of null when status is null and also need not to concatenate the status if there is a repetition of a product in the table..tried a lot, but couldn't sort out. Thanks in advance.

Upvotes: 3

Views: 64

Answers (1)

Mihai
Mihai

Reputation: 26804

This is a bit tricky because there will be repetion if statuses are the same,so the coalesce has to be on the outside of the 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;

Link

Upvotes: 2

Related Questions