CODEWITHSUNDEEP
mysqlpivot-tablemysql-5.7
John Beasley
John Beasley

Reputation: 3065

Create a dataset based on an ID and create columns for each corresponding date

I am trying to fathom what exactly is being asked by my management.

I have a MySQL 5.7 table that looks like this:

| email_id | source | message_id | submit_dt |
----------------------------------------------
|   id100  | google |   msg227   | 2024-11-12|
|   id200  | yahoo  |   msg227   | 2024-11-12|
|   id300  | google |   msg227   | 2024-11-12|
|   id100  | yahoo  |   msg227   | 2024-11-11|
|   id200  | google |   msg227   | 2024-11-11|
|   id300  | aol    |   msg227   | 2024-11-10|

I need to write a query that will take the above and make it look like this:

| email_id | submit_dt | source | message_id | submit_dt | source | message_id | repeat
-------------------------------------------------------------------------------------------
| id100    | 2024-11-12| google |   msg227   | 2024-11-11| yahoo  |   msg227   | 
-------------------------------------------------------------------------------------------
| id200    | 2024-11-12| yahoo  |   msg227   | 2024-11-11| google |   msg227   |
-------------------------------------------------------------------------------------------
| id300    | 2024-11-12| google |   msg227   | 2024-11-10| aol    |   msg227   |
-------------------------------------------------------------------------------------------

So essentially, I need to line up up each email_id in a single row and repeat every list the date, source, and message_id associated with the email_id.

I hope that makes sense.

This is what I attempted to no avail:

SELECT 
  GROUP_CONCAT(`email_id`) AS 'Email ID'
  , `submit_dt`
  , `source` 
  , `message_id`
FROM `submissions` 
WHERE `message_id` LIKE '240227' GROUP BY `email_id` ORDER BY `submissions`.`email_id` ASC;

I am getting the below error:

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 
'righters_db.submissions.submit_dt' which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

I don't even think the query will produce what I'm trying to achieve.

Please help me solve this or advise if what I am trying to do cannot be done.

Upvotes: 0

Views: 38

Answers (1)

ArtBindu
ArtBindu

Reputation: 1972

Technically this type of issue not possible. But if there're exactly two rows under email_id, then is can be possible through SQL query.

This is query to find exactly two records:

SELECT *,
  ROW_NUMBER() OVER (PARTITION BY email_id) AS email_index
FROM submissions
WHERE email_id IN (
  SELECT email_id 
  FROM ( 
      SELECT email_id,
         count(*) as count_email
      FROM submissions
      GROUP BY email_id
  ) T1
  WHERE count_email = 2
)
ORDER BY email_id ASC

This is final query where merged two records and show in a single row:

WITH submissions_f AS (
  SELECT *,
     ROW_NUMBER() OVER (PARTITION BY email_id) AS email_index
  FROM submissions
  WHERE email_id IN (
    SELECT email_id 
    FROM ( 
        SELECT email_id,
           count(*) as count_email
        FROM submissions
        GROUP BY email_id
    ) T
    WHERE count_email = 2
  )
  ORDER BY email_id ASC
)
SELECT T1.email_id,
  T1.submit_dt, T1.source, T1.message_id, 
  T2.submit_dt, T2.source, T2.message_id,
  IF((T1.source LIKE T2.source) and (T1.message_id LIKE T2.message_id), true, '') as repeat_data

FROM (SELECT * FROM submissions_f WHERE email_index = 1) T1
  INNER JOIN 
     (SELECT * FROM submissions_f WHERE email_index = 2) T2
  ON T1.email_id = T2.email_id;

Output according to your expection: enter image description here

db<>fiddle query for refference.

Please update your repeat_data column SQL Logic according to your expectation.

Upvotes: 0

Related Questions