Group Report With Header and Detail?

I have table transactions with below details

then I want to create report based on below report

Usually I will create a temporary table based on the report and add the row based on 2 queries.
Here's the logic

Loop // query1 retrieve all the category

   query2 retrieve all the details based on category

end of loop query1

Is there a more simple way to create this report?

query:

DROP TABLE IF EXISTS `transaction`;
CREATE TABLE `transaction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cashaccount_id` int(11) DEFAULT NULL,
  `dt_trans` date DEFAULT NULL,
  `category` varchar(200) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `type` tinyint(4) DEFAULT NULL COMMENT '0 income 1 expense',
  `amount` float(10,2) DEFAULT 0.00,
  `dt_created` datetime DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `transaction` (`id`, `cashaccount_id`, `dt_trans`, `category`, `description`, `type`, `amount`, `dt_created`, `uid`) VALUES
(115, 1, '2021-10-10', 'Expense', 'Food', 1, 60.00, '2021-10-10 17:06:36', 1),
(121, 1, '2021-10-15', 'Travel', 'Petrol', 1, 20.00, '2021-10-15 01:01:15', 1),
(123, 1, '2021-10-05', 'Travel', 'Petrol', 0, 20.00, '2021-10-15 09:11:28', 1)

Try to union three select statements with a specific order for each one as the following:

To get the report for all users:

with t as
(
  select id, dt_trans, category, description, amount, uid,
       dense_rank() over (order by uid, category) as ord1, 2 as ord2
  from transaction
  
  union all
  
  select distinct category, '', '', '', '', uid,
         dense_rank() over (order by uid, category) as ord1,
         1 as ord2
  from transaction 
  
  union all
  
  select  '', '', '', 'Total', 
          sum(amount), uid,
          dense_rank() over (order by uid, category) as ord1, 
          3 as ord2 
  from transaction 
  group by category, uid
)
select uid, id, dt_trans, category, description, amount
from t
order by uid, ord1, ord2

demo

To get the report for a single user:

with t as
(
  select id, dt_trans, category, description, amount,
       dense_rank() over (order by category) as ord1, 2 as ord2
  from transaction where uid = 1
  
  union all
  
  select distinct category, '', '', '', '',
         dense_rank() over (order by category) as ord1,
         1 as ord2
  from transaction where uid = 1
  
  union all
  
  select  '', '', '', 'Total', 
          sum(amount),
          dense_rank() over (order by category) as ord1, 
          3 as ord2 
  from transaction where uid = 1
  group by category 
)
select id, dt_trans, category, description, amount
from t
order by ord1, ord2

demo

But it would be better to perform this kind of formatting reports outside the SQL server.

Try use ROLLUP. Example

select category head,rn, max(dt_trans)dt_trans,category,max(description)description,sum(amount) amount
from(
select *
  ,row_number()over(partition by category order by category desc,dt_trans) rn
from transaction
) t
group by category ,rn with rollup

Result. There null in rn - totals by category, null in both head and rn - report totals.

You may try to loop on programming side but here is a simple mysql query

SELECT 
    s.*
FROM
    transaction s
        CROSS JOIN
    (SELECT 
        category
    FROM
        transaction) AS t
        GROUP BY s.id