Creating SQL Server (T-SQL) view that flattens nulls

user_15684173 0
Viewed 51

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:

Cat Qty City Cntry NewC RetCust TotC
Laptop 4 Berlin Germany 8 2 10
Laptop 3 London England 2 5 7
TV 5 London England 2 5 7
flatten view sql-server
2 Answers

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 
)
select Cat, Qty, City, Ctry, NewC, RetC,
    -- As per your example, you would no longer need GROUP BY,
    --   therefore SUM function should be removed
    SUM(IsNull(NewC, 0) + IsNull(RetC, 0)) as TotC
from CTE_FlattenNulls
-- As per your example, you would no longer need GROUP BY
group by Cat, Qty, City, Ctry
-- ,NewC -- Definitely not needed anymore
-- ,RetC -- Definitely not needed anymore

Everything else stays the same

user_63052940

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

Cat Qty City Country NewC RetCust TotC
Laptop 4 Berlin Germany 8 2 10
Laptop 3 London England 2 5 7
TV 5 London England 2 5 7
user_36116690
Related