group_concat and group by together

It is possible to get to the desired result in a simpler way.

First, you could start with this simple query:

SELECT
  t1.id,
  t2.env,
  CONCAT(t3.inftype,
         ': [',
         GROUP_CONCAT(t3.infname ORDER BY t3.infname SEPARATOR ', '),
         ']'
        ) AS inftypeandnames
FROM
  table1 AS t1
  INNER JOIN table2 AS t2 ON t1.id = t2.id
  INNER JOIN table3 AS t3 ON t2.infid = t3.infid
GROUP BY
  t1.id,
  t2.env,
  t3.inftype

This is what you would get as the result for your data samples:

id  env  inftypeandnames
--  ---  ---------------
1   D    Db: [kli]
1   D    Srv: [pop]
1   P    Db: [hgj, kjk]
1   P    Srv: [abc, xyz]

Next, you could use the above result as a derived table and group it by id, env, concatenating the inftypeandnames values, like this:

SELECT
  id,
  env,
  GROUP_CONCAT(inftypeandnames ORDER BY inftypeandnames DESC SEPARATOR ' ') AS inftypesandnames
FROM
  (
    SELECT
      t1.id,
      t2.env,
      CONCAT(t3.inftype,
             ': [',
             GROUP_CONCAT(t3.infname ORDER BY t3.infname SEPARATOR ', '),
             ']'
            ) AS inftypeandnames
    FROM
      table1 AS t1
      INNER JOIN table2 AS t2 ON t1.id = t2.id
      INNER JOIN table3 AS t3 ON t2.infid = t3.infid
    GROUP BY
      t1.id,
      t2.env,
      t3.inftype
  ) AS s
GROUP BY
  id,
  env

The result would be very close to what you want:

id  env  inftypesandnames
--  ---  ------------------------------
1   D    Srv: [pop] Db: [kli]
1   P    Srv: [abc, xyz] Db: [hgj, kjk]

In order to split the inftypesandnames values into separate columns based on the values of env, you could duplicate the above query, apply a filter on env to each copy (env = 'D', env = 'P' and so on if there can be others), join the results and return each subset's inftypesandnames value in its own column – same as Rolando has suggested, although I would argue that you might need a full outer join rather than an inner join if it is possible for some ids to have only D while for some others only P. Since MySQL does not support the FULL JOIN / FULL OUTER JOIN syntax and the usual workaround to that is a union of a left join and a right join, I suspect the final query in this case would make Rolando's brain hurt even more.

However, there is no need to resort to duplicating subqueries like that, because you can pivot the results using conditional aggregation. Instead of grouping by id, env in the outer query, you would be grouping by id only and conditionally apply GROUP_CONCAT to the inftypeandnames values based on env, like this:

SELECT
  id,
  GROUP_CONCAT(CASE env WHEN 'P' THEN inftypeandnames END ORDER BY inftypeandnames DESC SEPARATOR ' ') AS P,
  GROUP_CONCAT(CASE env WHEN 'D' THEN inftypeandnames END ORDER BY inftypeandnames DESC SEPARATOR ' ') AS D
FROM
  (
    SELECT
      t1.id,
      t2.env,
      CONCAT(t3.inftype,
             ': [',
             GROUP_CONCAT(t3.infname ORDER BY t3.infname SEPARATOR ', '),
             ']'
            ) AS inftypeandnames
    FROM
      table1 AS t1
      INNER JOIN table2 AS t2 ON t1.id = t2.id
      INNER JOIN table3 AS t3 ON t2.infid = t3.infid
    GROUP BY
      t1.id,
      t2.env,
      t3.inftype
  ) AS s
GROUP BY
  id
;

You can verify for yourself that the result of the last query would match your requirements:

id  P                               D
--  ------------------------------  --------------------
1   Srv: [abc, xyz] Db: [hgj, kjk]  Srv: [pop] Db: [kli]

The first thing you need is a messy query to create each column

FIRST PHASE OF QUERY

SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env;

YOUR SAMPLE DATA

mysql> DROP DATABASE IF EXISTS kumar_concat;
Query OK, 3 rows affected (0.03 sec)

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

mysql> USE kumar_concat
Database changed
mysql> CREATE TABLE table1
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO table1 VALUES (),(),();
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table2
    -> (
    ->     id INT NOT NULL,
    ->     env VARCHAR(10) NOT NULL,
    ->     infid INT NOT NULL,
    ->     PRIMARY KEY (id,infid)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO table2 (id,env,infid) VALUES
    -> (1,'P',10), (1,'P',11), (1,'P',20),
    -> (1,'P',12), (1,'D',21), (1,'D',22);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table3
    -> (
    ->     infid INT NOT NULL,
    ->     inftype VARCHAR(10) NOT NULL,
    ->     infname VARCHAR(10) NOT NULL,
    ->     PRIMARY KEY (infid)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO table3 (infid,inftype,infname) VALUES
    -> (10,'Srv','abc'), (20,'Srv','xyz'), (11,'Db','hgj'),
    -> (12,'Db','kjk'), (21,'Srv','pop'), (22,'Db','kli');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

FIRST PHASE OF QUERY EXECUTED

mysql> SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY env,inftype) A GROUP BY env;
+-----+------------------------------+
| env | tags                         |
+-----+------------------------------+
| D   | Srv [pop] Db [kli]           |
| P   | Srv [abc, xyz] Db [hgj, kjk] |
+-----+------------------------------+
2 rows in set (0.02 sec)

mysql>

Let's create each for each env value

QUERY

SELECT
    T1.tags P,T2.tags D
FROM
(SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env) T1,
(SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env) T2
WHERE T1.env='P'
AND T2.env='D';

QUERY EXECUTED

mysql> SELECT
    ->     T1.tags P,T2.tags D
    -> FROM
    -> (SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY env,inftype) A GROUP BY env) T1,
    -> (SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY env,inftype) A GROUP BY env) T2
    -> WHERE T1.env='P'
    -> AND T2.env='D';
+------------------------------+--------------------+
| P                            | D                  |
+------------------------------+--------------------+
| Srv [abc, xyz] Db [hgj, kjk] | Srv [pop] Db [kli] |
+------------------------------+--------------------+
1 row in set (0.05 sec)

mysql>

FINAL QUERY (with id wedged in)

SELECT
    T1.id,T1.tags P,T2.tags D
FROM
(SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY id,env,inftype) A GROUP BY id,env) T1
INNER JOIN
(SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY id,env,inftype) A GROUP BY id,env) T2 
USING (id) WHERE T1.env='P' AND T2.env='D';

FINAL QUERY (with id wedged in) EXECUTED

mysql> SELECT
    ->     T1.id,T1.tags P,T2.tags D
    -> FROM
    -> (SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY id,env,inftype) A GROUP BY id,env) T1
    -> INNER JOIN
    -> (SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY id,env,inftype) A GROUP BY id,env) T2
    -> USING (id) WHERE T1.env='P' AND T2.env='D';
+----+------------------------------+--------------------+
| id | P                            | D                  |
+----+------------------------------+--------------------+
|  1 | Srv [abc, xyz] Db [hgj, kjk] | Srv [pop] Db [kli] |
+----+------------------------------+--------------------+
1 row in set (0.08 sec)

mysql>

GIVE IT A TRY !!!