Question 20240 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql how to split columns according to the condition?

I have a db fiddle in which I have a table. In this table, there are codes and their descriptions which are on the same field. i.e. for example if the code is 101 then its description MDI Reset is also in the same column. These both are identified by an id 271, the id of the codes, and 285 is the id of the description.

Actual Table

+------------+--------+----------+---------------+
| id     | did    | source    |         code     |
+------------+--------+----------+---------------+
|    101 | 271    | 101       |        200300B8  |
+------------+--------+----------+---------------+
|    102 | 271    | 102       |        200300B9  |
+------------+--------+----------+---------------+
|    151 | 285    | MDI Reset |        200300B8  |
+------------+--------+----------+---------------+
|    152 | 285    | Parameterization | 200300B9  |
+------------+--------+----------+---------------+

Output I want

------+----------+-------------------------------+
| source 1  | source 2          |      code      |
+------------+--------+----------+---------------+
| 101       |  MDI Reset        |      200300B8  |
+------------+--------+----------+---------------+
| 102       |  Parameterization |      200300B9  |
+------------+--------+----------+---------------+

I want to put The description and its source side by side just like above.

Tags:

2

Answers


  1. 0

    Your table design is awkward, but we can handle your requirement via a self join:

    SELECT e1.source AS source1, e2.source AS source2, e1.code
FROM events e1
INNER JOIN events e2
    ON e2.code = e1.code
WHERE
    e1.source REGEXP '^[0-9]+$' AND
    e2.source NOT REGEXP '^[0-9]+$';

    The joining logic here matches pairs of records sharing the same code. The first record in the pair has a pure number as its source, while the second record does not have a pure number as its source.

    Here is a link to your updated SQL Fiddle.

    Login or Signup to reply.
  2. 0

    You can simply call the columns you specifically want to see and rename the titles of the columns within the statement.

    select dmbmbs as 'source 1', bm as 'source 2', mbbm as 'code' from sp_bmzd
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search