MySQL – Convert Row to Column and Concatenate Results

concatgroup-concatenationMySQL

I have 2 tables

table 1 – apps

-----------
id  | name
-----------
1   | abc
-----------
2   | sdf
-----------
3   | dfg
-----------

table 2 – contacts

------------------------
id  | cnt_type  | cnt_id
------------------------
1   | manager   |    20
------------------------
1   | supervisor |  32
------------------------
1   | teamlead   | 45
------------------------
1   | teamlead  | 54
------------------------
2   | manager    |   20
------------------------
2   | supervisor |  32
------------------------

I need a view as below where cnt_type rows are represented as columns and the corresponding values are concatenated and group by the cnt_type per id…

---------------------------------------
id  | manager | supervisor  | teamlead
--------------------------------------
1   | 20      |    32       | 45, 54
--------------------------------------
2   | 20      |    32       |
--------------------------------------

Any help is greatly appreciated.

Best Answer

PROPOSED QUERY

SELECT
    A.id,
    IFNULL(GROUP_CONCAT(DISTINCT B.cnt_id),'') 'manager',
    IFNULL(GROUP_CONCAT(DISTINCT C.cnt_id),'') 'supervisor',
    IFNULL(GROUP_CONCAT(DISTINCT D.cnt_id),'') 'teamlead'
FROM apps A
LEFT JOIN contacts B ON A.id = B.id AND B.cnt_type='manager'
LEFT JOIN contacts C ON A.id = C.id AND C.cnt_type='supervisor'
LEFT JOIN contacts D ON A.id = D.id AND D.cnt_type='teamlead'
WHERE ISNULL(B.cnt_id)+ISNULL(C.cnt_id)+ISNULL(D.cnt_id) < 3
GROUP BY A.id;

SAMPLE DATA

DROP DATABASE IF EXISTS kumar;
CREATE DATABASE kumar;
USE kumar
CREATE TABLE apps
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY (id)
);
CREATE TABLE contacts
(
    id INT NOT NULL,
    cnt_type VARCHAR(20) NOT NULL,
    cnt_id INT NOT NULL
);
INSERT INTO apps (name) VALUES ('abc'),('sdf'),('dfg');
INSERT INTO contacts (id,cnt_type,cnt_id) VALUES
(1,'manager',20), (1,'supervisor',32), (1,'teamlead',45),
(1,'teamlead',54), (2,'manager',20), (2,'supervisor',32);

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS kumar;
Query OK, 2 rows affected (0.05 sec)

mysql> CREATE DATABASE kumar;
Query OK, 1 row affected (0.00 sec)

mysql> USE kumar
Database changed
mysql> CREATE TABLE apps
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(20) NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE contacts
    -> (
    ->     id INT NOT NULL,
    ->     cnt_type VARCHAR(20) NOT NULL,
    ->     cnt_id INT NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO apps (name) VALUES ('abc'),('sdf'),('dfg');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO contacts (id,cnt_type,cnt_id) VALUES
    -> (1,'manager',20), (1,'supervisor',32), (1,'teamlead',45),
    -> (1,'teamlead',54), (2,'manager',20), (2,'supervisor',32);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

SAMPLE DATA IN TABLES

mysql> select * from apps;
+----+------+
| id | name |
+----+------+
|  1 | abc  |
|  2 | sdf  |
|  3 | dfg  |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from contacts;
+----+------------+--------+
| id | cnt_type   | cnt_id |
+----+------------+--------+
|  1 | manager    |     20 |
|  1 | supervisor |     32 |
|  1 | teamlead   |     45 |
|  1 | teamlead   |     54 |
|  2 | manager    |     20 |
|  2 | supervisor |     32 |
+----+------------+--------+
6 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT
    ->     A.id,
    ->     IFNULL(GROUP_CONCAT(DISTINCT B.cnt_id),'') 'manager',
    ->     IFNULL(GROUP_CONCAT(DISTINCT C.cnt_id),'') 'supervisor',
    ->     IFNULL(GROUP_CONCAT(DISTINCT D.cnt_id),'') 'teamlead'
    -> FROM apps A
    -> LEFT JOIN contacts B ON A.id = B.id AND B.cnt_type='manager'
    -> LEFT JOIN contacts C ON A.id = C.id AND C.cnt_type='supervisor'
    -> LEFT JOIN contacts D ON A.id = D.id AND D.cnt_type='teamlead'
    -> WHERE ISNULL(B.cnt_id)+ISNULL(C.cnt_id)+ISNULL(D.cnt_id) < 3
    -> GROUP BY A.id;
+----+---------+------------+----------+
| id | manager | supervisor | teamlead |
+----+---------+------------+----------+
|  1 | 20      | 32         | 45,54    |
|  2 | 20      | 32         |          |
+----+---------+------------+----------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

Related Solutions

Mysql – Use Create Trigger to create New table using calculated values from common groupid column values

We can use MySQL CREATE TABLE AS statement to create a TABLE_B from existing TABLE_A by copying the existing table's columns

CREATE TABLE TABLE_B AS   
SELECT  id, name, SUM(cost) AS 'cost', organization
     FROM TABLE_A            
     GROUP BY name, organization;

In MySQL, we cannot have after INSERT or UPDATE description together, like in Oracle unfortunately. So you have to create two TRIGGERs

delimiter //
CREATE TRIGGER update_tableb
 AFTER INSERT 
  ON TABLE_A FOR EACH ROW
     BEGIN

   -- variable declarations

   -- trigger code

    END;//
delimiter ;

Oracle 10g – Query to Concatenate Columns if Record Duplicate

Use partition by on the name column, and count the rows based on the order of id-s. Where the row number of the actual row is greater than 1, concatenate id and name, else just use the name.

with t as (
select 1 as id, 'john' as name from dual union all
select 2, 'david' from dual union all
select 3, 'zed' from dual union all
select 4, 'john' from dual union all
select 5, 'david' from dual union all
select 6, 'john' from dual union all
select 7, 'david' from dual union all
select 8, 'john' from dual union all
select 9, 'john' from dual
)
select 
  id, 
  case 
    when row_number() over (partition by name order by id) > 1 then id || ' ' || name
    else name
  end as name
from t order by id;

        ID NAME
---------- ----------------------------------------------
         1 john
         2 david
         3 zed
         4 4 john
         5 5 david
         6 6 john
         7 7 david
         8 8 john
         9 9 john

9 rows selected.

To treat john, JOHN, joHN, etc. equally, simply change the partition by column, e.g upper(name).

select 
  id, 
  case 
    when row_number() over (partition by upper(name) order by id) > 1 then id || ' ' || name
    else name
  end as name
from t order by id;