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

merging multiple rows into one row with different columns sql – Mysql

I have two tables

Account table

id   |   account_no  
-----------------------    
1    | 111 
2    | 222

Account details

id   |   act_id (fk) | amount | created_dt_ | created_by
------------------------------------------------    
1    | 1             | 10       | 2022-10-30 | SYSTEM
2    | 1             | 100      | 2022-11-05 | user1
3    | 1             | 144      | 2022-11-10 | user2
4    | 1             | 156      | 2022-11-16 | user3
5    | 2             | 50      | 2022-11-05 | SYSTEM
6    | 2             | 51      | 2022-11-10 | user2
7    | 3             | 156      | 2022-11-16 | SYSTEM

I need a query to fetch only rows from account details which has at least 2 records for an account id, and merge those rows to a single row showcasing the initial amount and user who created it and the last amount and who created it, something like this

act_id | ini_amt | ini_dt | ini_usr  | fnl_amt | fnl_dt | fnl_usr
-------------------------------------------------------------------------------------    
1      | 10   | 2022-10-30 | SYSTEM  | 156 | 2022-11-16 | user3
2      | 50   | 2022-11-05 | SYSTEM  | 51  | 2022-11-10 | user2

we need only the rows with more than one records. How do i fetch that?

Tags:

3

Answers


  1. 0

    In MySQL 8 you could do it like this.

    If you need also information fom account, you simle can join it

    CREATE TABLE Account 
    (`id` int, `account_no` int)
;
    
INSERT INTO Account 
    (`id`, `account_no`)
VALUES
    (1, 111),
    (2, 222)
;


    Records: 2  Duplicates: 0  Warnings: 0

    CREATE TABLE Account_details
    (`id` int, `act_id` int, `amount` int, `created_dt_` varchar(10), `created_by` varchar(6))
;
    
INSERT INTO Account_details
    (`id`, `act_id`, `amount`, `created_dt_`, `created_by`)
VALUES
    (1, 1, 10, '2022-10-30', 'SYSTEM'),
    (2, 1, 100, '2022-11-05', 'user1'),
    (3, 1, 144, '2022-11-10', 'user2'),
    (4, 1, 156, '2022-11-16', 'user3'),
    (5, 2, 50, '2022-11-05', 'SYSTEM'),
    (6, 2, 51, '2022-11-10', 'user2'),
    (7, 3, 156, '2022-11-16', 'SYSTEM')
;

    Records: 7  Duplicates: 0  Warnings: 0

    WITH CTE_MIN as(
 SELECT
  `act_id`, `amount`, `created_dt_`, `created_by`,
  ROW_NUMBER() OVER(PARTITION BY `act_id` ORDER BY `created_dt_` ASC,`id` ASC) rn
  FROM Account_details),
   CTE_MAX as(
 SELECT
  `act_id`, `amount`, `created_dt_`, `created_by`,
  ROW_NUMBER() OVER(PARTITION BY `act_id` ORDER BY `created_dt_` DESC,`id` DESC) rn
  FROM Account_details)
SELECT
  mi.`act_id`, mi.`amount`, mi.`created_dt_`, mi.`created_by`, ma.`amount`, ma.`created_dt_`, ma.`created_by`
  FROM
CTE_MIN mi JOIN CTE_MAX ma 
  ON mi.`act_id` = ma.`act_id` 
  AND mi.rn = ma.rn  
  AND  mi.created_dt_!=ma.created_dt_
AND ma.rn = 1 ANd mi.rn = 1
    act_id amount created_dt_ created_by amount created_dt_ created_by
    1 10 2022-10-30 SYSTEM 156 2022-11-16 user3
    2 50 2022-11-05 SYSTEM 51 2022-11-10 user2

    fiddle

    Login or Signup to reply.
  2. 0

    On older MySQL version which doesn’t support windows functions:

    select act_id,
       max(case when new_col='min_value' then amount     end) as ini_amt,
       max(case when new_col='min_value' then created_dt end) as ini_dt,
       max(case when new_col='min_value' then created_by end) as ini_usr,
       max(case when new_col='max_value' then amount     end) as fnl_amt,
       max(case when new_col='max_value' then created_dt end) as fnl_dt,
       max(case when new_col='max_value' then created_by end) as fnl_usr
 from (  

        select ad.id,ad.act_id,ad.amount,ad.created_dt,ad.created_by,'max_value' as new_col
        from AccountDetails ad
        inner join (select act_id,max(created_dt) as max_created_dt
                     from AccountDetails
                     group by act_id
                     having count(*) >=2
                   ) as max_val on max_val.act_id =ad.act_id and max_val.max_created_dt=ad.created_dt
    union 
        select ad1.id,ad1.act_id,ad1.amount,ad1.created_dt,ad1.created_by,'min_value'
        from AccountDetails ad1
        inner join (select act_id,min(created_dt) as min_created_dt
                     from AccountDetails
                     group by act_id
                     having count(*) >=2
                   ) as min_val on min_val.act_id =ad1.act_id and min_val.min_created_dt=ad1.created_dt
  ) as tbl
group by act_id;

    https://dbfiddle.uk/q2Oxq0Ay

    Login or Signup to reply.
  3. 0

    We can do this without CTEs, using window functions and conditional aggregation:

    select act_id,
    max(case when rn_asc  = 1 then amount     end)  ini_amount,
    max(case when rn_asc  = 1 then created_dt end)  ini_created_dt,
    max(case when rn_asc  = 1 then created_by end)  ini_created_by,
    max(case when rn_desc = 1 then amount     end)  fnl_amount,
    max(case when rn_desc = 1 then created_dt end)  fnl_created_dt,
    max(case when rn_desc = 1 then created_by end)  fnl_created_by
from(
    select ad.*,
        row_number() over(partition by act_id order by created_dt     ) rn_asc,
        row_number() over(partition by act_id order by created_dt desc) rn_desc,
        count(*)     over(partition by act_id) cnt 
    from account_details ad
) ad
where 1 in (rn_asc, rn_desc) and cnt > 1
group by act_id

    In the subquery, row_number ranks records of the same account by ascending and descending date, while count checks how many records the account has.

    Then, the outer query filters on accounts that have more than one record, and on the top/bottom record. We can then pivot the dataset with group by and conditional expressions to produce the expected result.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search