Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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...

share|improve this question
3  
You really should consider normalizing this to avoid having to do string splitting. –  JNK Mar 7 '12 at 14:22
1  
Are you fixing the database design with this SQL, or just working around it? You are storing 2 facts in one column... –  gbn Mar 7 '12 at 14:31
    
Working around an existing broken design :( Can't refactor as it is a third party database. We are not using the product exactly how we should. None of this is down to me but I am the one who needs to work out another work around rather than fixing the underlying problem. –  Clive Mar 7 '12 at 14:51
add comment

2 Answers 2

up vote 4 down vote accepted

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
share|improve this answer
    
It works (I had to add insert into @T values a few times). Trying to work exactly how it is doing it. –  Clive Mar 7 '12 at 15:34
1  
@Clive – The first cross apply uses replace to change your value to Web.Electronics. The second cross apply uses parsename to split the string into two parts. Then there is a group by on Department and in the field list there is one count for Web and one for Store values. –  Mikael Eriksson Mar 7 '12 at 16:03
add comment

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
*/
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.