query to fetch data

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,

SreelathaThe 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|||SELECT Task, Coalesce(Max(NullIf(Employee, 'Manager')), 'Manager')
FROM tbl
GROUP BY Task

Mr Tea

"sreelatha" <sreelatha@.hotmail.com> wrote in message
news:1110821575.186916.111250@.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

Query to combine several "records/rows" into one "record/row"?

Im new to SQL, Ive been getting by building lists from our student database by building or teacher (etc.). These have been all records that exist on only one row, so its been easy (so far).
Heres what Im trying to do and I dont even know what to call it, so I'm not even sure what to search for...

Ive got a MS SQL 6.5 database with the following:
ACTIVE students: each student has ID_NUM[8 digits], NAME, GRADE, SCHOOL with one rowof 4 data items per student.

SCHEDULE of courses with (student) ID_NUM[8 digits], SEMESTER[S1 or S2], HOUR[1-7], COURSE_NAME, ROOM_NUM with 14 records (rows) with these 5 items, in this SCHEDULE database for each student.

My mission is to combine then in to one row using the student ID_NUM as the key. (This is to help me with several things, spreadsheets/database for others to easily use, export to simple databases for teacher handhelds.).

Id like one row of the 75 items combined, resulting in 32 items (ACTIVE 4 items + 14 * 2 SCHEDULE items [COURSE_NAME, ROOM_NUM]) since I want stuff plugged into the correct field, for each student. I'd refer to this as COMBINEDRECORD and Id turn the field names into the following:
ID_NUM[8 digits], NAME, GRADE, SCHOOL, S1HOUR1_NAME, S1HOUR1_ROOM_NUM, S1HOUR2_NAME, S1HOUR2_ROOM_NUM, S1HOUR3_NAME, S1HOUR3_ROOM_NUM, S1HOUR4_NAME, S1HOUR4_ROOM_NUM, S1HOUR5_NAME, S1HOUR5_ROOM_NUM, S1HOUR6_NAME, S1HOUR6_ROOM_NUM, S1HOUR7_NAME, S1HOUR7_ROOM_NUM, S2HOUR1_NAME, S2HOUR1_ROOM_NUM, S2HOUR2_NAME, S2HOUR2_ROOM_NUM, S2HOUR3_NAME, S2HOUR3_ROOM_NUM, S2HOUR4_NAME, S2HOUR4_ROOM_NUM, S2HOUR5_NAME, S2HOUR5_ROOM_NUM, S2HOUR6_NAME, S2HOUR6_ROOM_NUM, S2HOUR7_NAME, S2HOUR7_ROOM_NUM

I dont care if there are any blanks I just want to get the data if
SEMESTER='S2', HOUR='5' & COURSE_NAME='Basketweaving' & ROOM_NUM='Pool'
to end up being in the right spot (S2 and Hour 5) in the new COMBINEDRECORD row with
S2HOUR5_NAME='Basketweaving' & S2HOUR5_ROOM_NUM='Pool' for the correct student ID_NUM. Of course with the correct ACTIVE student info into the same "row"

Does that make sense? It might not be the best way, but itll make the data more accessible to everyone and some programs we already use with our old student system. Obviously theres more data than that but I think this is enough to explain my issue and give me enough to work with

Any help, directions to a webpage or book with the correct terms to look up would be very helpful.

Thank you for any help or direction you can give,
GarySounds like a join. Are you trying to creat a new table or just do a report?|||Or a view with a join if you want to leave the existing tables alone.|||Originally posted by barneyrubble318
Or a view with a join if you want to leave the existing tables alone.

I'll probably be doing two (similar) things:

1) An SQL query that just puts my COMBINEDRECORD table into an Excel Spreadsheet. (Why Excel? Everyone here knows how to merge from it, so they can then manipulate it how they want.)

2) An SQL query from Desktop2MobileDB which will convert the COMBINEDRECORD table into a Palm OS (MobileDB) database so principals and teachers can have more data on hand. (They can see where the kid in the hall is really supposed to be...)

I pretty much do the above two things with data now, the problem is the multi-line data from the SCHEDULE/

If I have to create a new table and then access it from there, I guess I can do that. I might not be able to automate it as easily though...

Thanks,
Gary