merging multiple rows into one row with different columns sql

Question

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?

Answer 1

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