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

Mysql – Divide results of a query in two different columns

I have the following result of a query:
P2_AU_CE P2_03
P2_AU_CE P2_04
P2_CN_MOCE_001 P2_04

I would like to have the result shown this way instead:
P2_AU_CE P2_03 P2_04
P2_CN_MOCE EMPTY P2_04
Tags:

3

Answers


  1. 0

    Try this SQL query to get the results you want. In this query, in the first column the rows are grouped by the values, and for the conditional aggregation is used to spread the values from second column into the separate column. The empty cells are replaced with the text "EMPTY" by using the COALESCE function.

    SELECT
    first_column AS P2_AU_CE,
    COALESCE(MAX(CASE WHEN second_column = 'P2_03' THEN second_column END), 'EMPTY') AS P2_03,
    COALESCE(MAX(CASE WHEN second_column = 'P2_04' THEN second_column END), 'EMPTY') AS P2_04
FROM your_table
GROUP BY first_column;

    Hope it works 🙂

    Login or Signup to reply.
  2. 0

    To achieve the desired result where you want to pivot the data and show it in a different format, you can use SQL to pivot the rows into columns. Since the values in the second column ("P2_03" and "P2_04") should become column headers, you can use conditional aggregation to achieve this. Here’s the SQL query to pivot your data:

    SELECT 
LEFT(column1, CHARINDEX('_', column1 + '_') - 1) AS Col1,
MAX(CASE WHEN column2 = 'P2_03' THEN column2 ELSE 'EMPTY' END) AS P2_03,
MAX(CASE WHEN column2 = 'P2_04' THEN column2 ELSE 'EMPTY' END) AS P2_04
FROM your_table_name
GROUP BY LEFT(column1, CHARINDEX('_', column1 + '_'))

    Please replace your_table_name with the actual name of your table. This query uses the LEFT and CHARINDEX functions to extract the first part of column1 before the underscore, and then it uses conditional aggregation to pivot the data into the format you want. It will display "EMPTY" if there is no corresponding value in column2 for a particular combination of Col1.

    The result will look like this:

        Col1    | P2_03 | P2_04
----------------------------
P2_AU_CE    | P2_03 | P2_04
P2_CN_MOCE  | EMPTY | P2_04

    This query will give you the desired output where the values in column2 become separate columns.

    Login or Signup to reply.
  3. 0

    You can try using the STRING_AGG() function to join the non-null values in your P2_03 and P2_04 columns to a single string, that is separated by a slash ‘|’.

    But if the value is ‘NULL`, you will then use the string ‘EMPTY’.

    SELECT
    "P2_AU_CE",
    STRING_AGG(CASE WHEN "P2_03" IS NOT NULL THEN "P2_03" ELSE 'EMPTY' END, ' | ') AS "P2_03",
    STRING_AGG(CASE WHEN "P2_04" IS NOT NULL THEN "P2_04" ELSE 'EMPTY' END, ' | ') AS "P2_04"
FROM
    your_table
GROUP BY
    "P2_AU_CE"
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search