Join on existing aggregate query to pivot result without id

On a Sql-Server instance, I have three tables:

ActionItem

Id	Name
1	Fish
2	Gravy
3	Pants

ActionData

Id	ActionId	Group	Field	Value
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

Costing

Id	ActionId	Break	Cost
1	1	Normal	11.3
2	1	Sub	54
3	1	Premium	0.4
4	3	Normal	22
5	3	Premium	0.67

I have a query that sums the cost for each ActionItem:

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

Nice and straight-forward:

Id	Name	(No column name)
1	Fish	65.7
2	Gravy	NULL
3	Pants	22.67

I created a pivot too:

select * from
(select [ActionId], [Group], [Field], [Value] from ActionData) src
pivot (max([Value]) for [ActionId] in ([1],[2],[3],[4])) ppp

Which gets me data in the right format:

Group	Field	1	2	3	4
1	1	100	50	NULL	NULL
1	2	200	60	NULL	NULL
1	3	300	70	NULL	NULL
1	4	NULL	NULL	NULL	NULL
1	5	NULL	NULL	NULL	NULL
2	6	“Some Text”	NULL	NULL	NULL

But I cannot join these two queries together because that PIVOT doesn’t contain the ActionId … even though I use Select * from – how can I get the ActionId col to show on my pivoted data, so I can join it to the rest of my original query?

I could not get sqlfiddle.com to work for MS SQL SERVER today but here are create and inserts if anyone’s interested:

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] FLOAT);

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

Answer

Not sure what output you expect.
But here’s an attempt to join the two queries in 1 pivot.

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
GO

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

db<>fiddle here