merging multiple rows into one row with different columns sql-CodePudding

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?

CodePudding user response：

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) 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) 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

CodePudding user response：

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.

CodePudding user response：

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