SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE ActionItem
    ([Id] int, [Name] varchar(5));
    
INSERT INTO ActionItem
    ([Id], [Name])
VALUES
    (1, 'Fish'),
    (2, 'Gravy'),
    (3, 'Pants');

CREATE TABLE ActionData (
    [Id] int, 
    [ActionId] int, 
    [Group] int, 
    [Field] int, 
    [Value] varchar(11)
);
    
INSERT INTO ActionData
    ([Id], [ActionId], [Group], [Field], [Value])
VALUES
    (1, 1, 1, 1, '100'),
    (2, 1, 1, 2, '200'),
    (3, 1, 1, 3, '300'),
    (4, 1, 1, 4, NULL),
    (5, 1, 1, 5, NULL),
    (6, 1, 2, 6, 'Some Text'),
    (7, 2, 1, 1, '50'),
    (8, 2, 1, 2, '60'),
    (9, 2, 1, 3, '70')
;

CREATE TABLE Costing (
  [Id] int,
  [ActionId] int,
  [Break] VARCHAR(9),
  [Cost] decimal(18,2)
);

INSERT INTO Costing
  ([Id], [ActionId], [Break], [Cost])
VALUES
  ('1', '1', 'Normal', '11.3'),
  ('2', '1', 'Sub', '54'),
  ('3', '1', 'Premium', '0.4'),
  ('4', '3', 'Normal', '22'),
  ('5', '3', 'Premium', '0.67');
17 rows affected

select c.ActionId, ai.Name, c.[Break]
, sum(c.Cost) as TotalCost
from Costing c 
left join ActionItem ai
  on ai.Id = c.ActionId 
group by c.ActionId, ai.Name, c.[Break]


ActionId
Name
Break
TotalCost


1
Fish
Normal
11.30

1
Fish
Premium
0.40

1
Fish
Sub
54.00

3
Pants
Normal
22.00

3
Pants
Premium
0.67


select pvt.* 
from
(
  select d.ActionId, ai.Name
  --, d.[Group]
  , cast(d.[Field] as varchar(30)) as [Col]
  , try_cast(d.[Value] as float) as [Value]
  from ActionData d
  left join ActionItem ai on ai.Id = d.ActionId
  where isnumeric(d.[Value]) = 1
  
  union all
  
  select c.ActionId, ai.Name
  --, 1 as [Group]
  , c.[Break] as [Col]
  , sum(c.Cost) as TotalCost
  from Costing c 
  left join ActionItem ai
    on ai.Id = c.ActionId 
  group by c.ActionId, ai.Name, c.[Break]
) src
pivot (
  max([Value])
  for [Col] in ([1],[2],[3],[4],[Normal],[Premium],[Sub])
) pvt


ActionId
Name
1
2
3
4
Normal
Premium
Sub


1
Fish
100
200
300
null
11.3
0.4
54

2
Gravy
50
60
70
null
null
null
null

3
Pants
null
null
null
null
22
0.67
null

ActionId	Name	Break	TotalCost
1	Fish	Normal	11.30
1	Fish	Premium	0.40
1	Fish	Sub	54.00
3	Pants	Normal	22.00
3	Pants	Premium	0.67

ActionId	Name	1	2	3	4	Normal	Premium	Sub
1	Fish	100	200	300	null	11.3	0.4	54
2	Gravy	50	60	70	null	null	null	null
3	Pants	null	null	null	null	22	0.67	null