- Stack Exchange Data Explorer

declare @Data table (cat varchar(1), subcat int, amount ...

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

-- Enter Query Title
-- Enter Query Description

declare @Data table (cat varchar(1), subcat int, amount int)
declare @Config table (cat varchar(1), subcat int)
insert into @Data values ('A', 1, 123)
insert into @Data values ('A', 2, 456)
insert into @Data values ('B', 1, 222)
insert into @Data values ('B', 2, 333)
insert into @Config values ('B', 1)

SELECT d.cat,
       d.subcat, 
       SUM(CASE 
           WHEN c.subcat IS NULL OR c.subcat = d.subcat 
           THEN d.amount 
           ELSE 0 
        END) as Amount
FROM @Data d
    LEFT OUTER JOIN @Config c ON (d.cat = c.cat)
GROUP BY d.cat, d.subcat
ORDER BY d.cat

created dec 6 11