query to fetch data

[sreelatha]

Hi,

I have a table with two columns Task and Employee. It lists all the
tasks and the assigned employees. The same task will have multiple
roles with an employeename or 'manager' as the data.

If I have the following data

'sales', 'john'
'sales', 'manager'
'dev', 'manager'
'make_coffee', 'manager'
'browse', 'jane'
'browse', 'manager'

I need to get the rows wherever an employee is named (sales and browse
for example) and get manager for the rest.

I can make it in two queries. Look for not manager in one and then for
manager. Is there anyway to get them in a single query?

If I need to look for 'sales', I need to get 'john' and not 'manager'.
How to do that in a single query?

Another need is to list all tasks with assigned. So for the above, I
should get the following list

'sales', 'john'
'dev', 'manager'
'make_coffee', 'manager'
'browse', 'jane'

with two queries, I will get

'sales', 'john'
'browse', 'jane'

'dev', 'manager'
'make_coffee', 'manager'

which is ok. Order/sequence is not important.

TIA,

Sreelatha

[sreelatha]

The code to create and insert data
CREATE TABLE tbl (
task nvarchar(20) NOT NULL,
employee nvarchar(20) NOT NULL)
GO

insert into tbl values('sales', 'john')
insert into tbl values('sales', 'manager')
insert into tbl values('dev', 'manager')
insert into tbl values('make_coffee', 'manager')
insert into tbl values('browse', 'jane')
insert into tbl values('browse', 'manager' )
go

[Lee Tudor]

SELECT Task, Coalesce(Max(NullIf(Employee, 'Manager')), 'Manager')
FROM tbl
GROUP BY Task

Mr Tea

"sreelatha" <sr*******@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Hi,

I have a table with two columns Task and Employee. It lists all the
tasks and the assigned employees. The same task will have multiple
roles with an employeename or 'manager' as the data.

If I have the following data

'sales', 'john'
'sales', 'manager'
'dev', 'manager'
'make_coffee', 'manager'
'browse', 'jane'
'browse', 'manager'

I need to get the rows wherever an employee is named (sales and browse
for example) and get manager for the rest.

I can make it in two queries. Look for not manager in one and then for
manager. Is there anyway to get them in a single query?

If I need to look for 'sales', I need to get 'john' and not 'manager'.
How to do that in a single query?

Another need is to list all tasks with assigned. So for the above, I
should get the following list

'sales', 'john'
'dev', 'manager'
'make_coffee', 'manager'
'browse', 'jane'

with two queries, I will get

'sales', 'john'
'browse', 'jane'

'dev', 'manager'
'make_coffee', 'manager'

which is ok. Order/sequence is not important.

TIA,

Sreelatha