Объединение нескольких строк в одну строку с разными столбцами sql

В MySQL 8 вы можете сделать это так.

Если вам также нужна информация из учетной записи, вы можете просто присоединиться к ней.

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

рабочий пример

В более старой версии MySQL, которая не поддерживает функции Windows:

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

Мы можем сделать это без CTE, используя оконные функции и условную агрегацию:

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

В подзапросе row_number ранжирует записи одной и той же учетной записи по возрастанию и убыванию даты, а count проверяет количество записей в учетной записи.

Затем внешний запрос фильтрует учетные записи, которые имеют более одной записи, а также верхнюю/нижнюю запись. Затем мы можем повернуть набор данных с помощью group by и условных выражений, чтобы получить ожидаемый результат.