Home > OS >  Join on existing aggregate query to pivot result without id
Join on existing aggregate query to pivot result without id

Time:11-26

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

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

  • Related