I'm having a lot of difficulty trying to create a view that flattens the data without nulls. I've supplied the code that creates two basic tables and my view code so you can see what I've tried so far. Please note that the two tables do not have a matching primary or foreign key column, so the summary in the view is created by just joining on City. I can't use XML because my team of data analysts all have intermediate skills and won't be able to understand it. I considered using a recursive CTE, but I can't get it right. The result produces 6 lines but I want 3 lines. Thanks for any ideas about a better way to achieve this.

CREATE TABLE A (
    OrdID int,
    Cat varchar(255),
    Qty int,
    City varchar(255),
    Ctry varchar(255)
);
INSERT INTO A (OrdID, Cat, Qty, City, Ctry)
VALUES (1, 'TV', 5,'London',  'England');
INSERT INTO A (OrdID, Cat, Qty, City, Ctry)
VALUES (2, 'Laptop', 3,'London', 'England');
INSERT INTO A (OrdID, Cat, Qty, City, Ctry)
VALUES (3, 'Laptop', 4, 'Berlin', 'Germany');

CREATE TABLE Cust (
    CustID int,
    CustType varchar(255),
    City varchar(255),
    NumItems int,
);
INSERT INTO Cust (CustID, CustType, City, NumItems)
VALUES (1, 'New', 'London', 2);
INSERT INTO Cust (CustID, CustType, City, NumItems)
VALUES (2, 'Returning','London', 5);
INSERT INTO Cust (CustID, CustType, City, NumItems)
VALUES (3, 'Returning','Berlin', 2);
INSERT INTO Cust (CustID, CustType, City, NumItems)
VALUES (4, 'New','Berlin', 8);

alter view My_View
as

With CTE_FlattenNulls
as
(
Select 
S.Cat
, S.Qty
, S.City
, S.Ctry 
, case when C.CustType like 'New' then sum(C.NumItems) end as NewC
, case when C.CustType like 'Returning' then sum(C.NumItems) end as RetC
from A as S
left join Cust as C
on S.City = C.City
group by 
S.Cat
, S.Qty
, S.City
, S.Ctry
, C.CustType
)
select 
Cat
,Qty
,City
,Ctry
,NewC
,RetC
,SUM(IsNull(NewC, 0)   IsNull(RetC, 0)) as TotC
from CTE_FlattenNulls
group by
Cat
,Qty
,City
,Ctry
,NewC
,RetC

go

Just adding the output that I wanted:

CodePudding user response：

You were very close. See comments in code for explanation.

With CTE_FlattenNulls
as
(
Select S.Cat, S.Qty, S.City, S.Ctry,
    -- To do conditional summation case expression needs to be inside the SUM function
    sum( case when C.CustType like 'New' then C.NumItems else 0 end ) as NewC,
    sum( case when C.CustType like 'Returning' then C.NumItems else 0 end ) as RetC
from A as S
left join Cust as C
on S.City = C.City
group by 
S.Cat
, S.Qty
, S.City
, S.Ctry
-- then you do not need to group by this column and therefore you do not get extra rows
--, C.CustType 
)

Everything else stays the same

CodePudding user response：

To get to your result, why can you not just do a simple group by with conditional sum ?
It has no need for a CTE

See this example, also in this DBFiddle

select A.Cat,
       A.Qty,
       A.City,
       min(A.Ctry) as Country,
       sum(case when C.CustType = 'New' then C.NumItems else 0 end) as NewC,
       sum(case when C.CustType = 'Returning' then C.NumItems else 0 end) as RetCust,
       sum(C.NumItems) as TotC
from   A
  join Cust C on A.City = C.City
group by A.Cat,
         A.Qty,
         A.City
order by A.Cat, A.City

it returns this