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

ActionItem

ActionData

Costing

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:

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:

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

CodePudding user response：

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