Splitting a column in two and returning a summary of results

Question

Lets take a very simple table

Department           | Staff Name
---------------------|---------------
Web - Electronics    | Matthew
Store - Electronics  | Mark
Web - Home           | Luke
Store - Home         | John
Store - Home         | Paul
Store - Home         | Simon
Web - Electronics    | Peter

I need to work out some SQL to return the following:

Deparment    | Web  | Store
-------------|------|---------
Electronics  | 2    | 1
Home         | 1    | 3

So I need to do a count of web and store for each department. Is it possible to do something like this in SQL?

In C# I would loop through the results. I would split the deparment into two strings on the -. I would add an entry in a dictonary so the deparment name is the key. For the entry I would have a small object with a field of Web and Store. I would then increment the value by one if the entry is stor or web.

After this I would then loop throgu hmy dictiaonry to output the result by priting the dictionary key then printing the count for web and store.

I am not even sure where to begin trying to do this in just SQL...

Answer 1

You could do something like this.

declare @T table
(
  Department varchar(50),
  [Staff Name] varchar(50)
)

insert into @T values
('Web - Electronics',    'Matthew'),
('Store - Electronics',  'Mark'),
('Web - Home',           'Luke'),
('Store - Home',         'John'),
('Store - Home',         'Paul'),
('Store - Home',         'Simon'),
('Web - Electronics',    'Peter')

select T3.Department,
       sum(case T3.WebStore when 'Web' then 1 else 0 end) as Web,
       sum(case T3.WebStore when 'Store' then 1 else 0 end) as Store
from @T as T1
  cross apply (select replace(Department, ' - ', '.')) as T2(Department)
  cross apply (select parsename(T2.Department, 1),  
                      parsename(T2.Department, 2)) as T3(Department, WebStore)
group by T3.Department

Answer 2

You may like to do this in two stages using a view as an intermediate, for example:

testbed:

create table staff(name varchar(50) not null, department varchar(50) not null);
go
create view v_staff as
select name, parsename(department,1) as department, parsename(department,2) as type 
from (select name, replace(department, ' - ', '.') as department from staff) z;
go
insert into staff(name, department) values 
('Matthew','Web - Electronics'),
('Mark','Store - Electronics'),
('Luke','Web - Home'),
('John','Store - Home'),
('Paul','Store - Home'),
('Simon','Store - Home'),
('Peter','Web - Electronics');

query:

select department, sum([Web]) as web, sum([Store]) as store
from v_staff pivot (count(type) for type in([Web], [Store])) as z
group by department;

result:

/*
department  web store
Electronics 2   1
Home        1   3
*/