Re: A Query Question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Raider (sraider_at_yandex.ru)
Date: 03/12/04 

Date: Fri, 12 Mar 2004 12:17:54 +0300

try this:

declare @eid int, @e nvarchar(16)
declare @rep table (employee_name nvarchar(16), department_list
nvarchar(1000))

set nocount on

declare elist cursor fast_forward for
  select [id], employee_name from employee
open elist

while 1=1
begin
  fetch next from elist into @eid, @e
  if @@fetch_status <> 0 break

declare @list nvarchar(1000), @dp nvarchar(16)
set @list = N''
declare dlist cursor fast_forward for
  select department_name from department inner join employee_department on
department.[id]=employee_department.department_id
  where employee_department.employee_id = @eid
open dlist
while 1=1
begin
  fetch next from dlist into @dp
  if @@fetch_status <> 0 break
  if @list <> N'' set @list=@list+N','
  set @list=@list+@dp
end
close dlist
deallocate dlist

insert into @rep values (@e, @list)

end

close elist
deallocate elist

set nocount off

select * from @rep

"Tomas" <stranger@work.com> сообщил/сообщила в новостях следующее:
news:#o774E7BEHA.3568@tk2msftngp13.phx.gbl...
> I have a question that some of you nice folks might be able to help with.
> First here is some quick test data. In the real data there will be a lot
> more columns in each of the 'employee' and 'department' tables though:
>
> -------------
> CREATE TABLE [department] (
> [id] [int] NOT NULL ,
> [department_name] [nvarchar] (16) NOT NULL
> )
>
> CREATE TABLE [employee] (
> [id] [int] NOT NULL ,
> [employee_name] [nvarchar] (16) NOT NULL
> )
>
> CREATE TABLE [employee_department] (
> [employee_id] [int] NOT NULL ,
> [department_id] [int] NOT NULL
> )
>
> INSERT INTO department (id, department_name) VALUES (1, 'Production')
> INSERT INTO department (id, department_name) VALUES (2, 'Marketing')
> INSERT INTO department (id, department_name) VALUES (3, 'Maintenance')
> INSERT INTO department (id, department_name) VALUES (4, 'Management')
>
> INSERT INTO employee (id, employee_name) VALUES (1, 'James')
> INSERT INTO employee (id, employee_name) VALUES (2, 'Susan')
> INSERT INTO employee (id, employee_name) VALUES (3, 'Paula')
> INSERT INTO employee (id, employee_name) VALUES (4, 'Mark')
> INSERT INTO employee (id, employee_name) VALUES (5, 'Ann')
> INSERT INTO employee (id, employee_name) VALUES (6, 'Louis')
> INSERT INTO employee (id, employee_name) VALUES (7, 'Karen')
> INSERT INTO employee (id, employee_name) VALUES (8, 'Danny')
> INSERT INTO employee (id, employee_name) VALUES (9, 'Eric')
>
> INSERT INTO employee_department (employee_id, department_id) VALUES (1, 1)
> INSERT INTO employee_department (employee_id, department_id) VALUES (2, 4)
> INSERT INTO employee_department (employee_id, department_id) VALUES (3, 2)
> INSERT INTO employee_department (employee_id, department_id) VALUES (3, 4)
> INSERT INTO employee_department (employee_id, department_id) VALUES (4, 3)
> INSERT INTO employee_department (employee_id, department_id) VALUES (5, 1)
> INSERT INTO employee_department (employee_id, department_id) VALUES (7, 1)
> INSERT INTO employee_department (employee_id, department_id) VALUES (7, 2)
> INSERT INTO employee_department (employee_id, department_id) VALUES (7, 3)
> INSERT INTO employee_department (employee_id, department_id) VALUES (9, 1)
> -------------
>
> I would like to have a query that could ouput something like this
>
> -------------
> James 'Production'
> Susan 'Managment'
> Paula 'Marketing,Management'
> Mark 'Maintenance'
> Ann 'Production'
> Louis ''
> Karen 'Production,Marketing,Maintenance'
> Danny ''
> Eric 'Production'
> -------------
>
> Can this be done effectively in a query or would it be better to pull all
> data and group it in the client? In most cases there will be just one
> 'department' per 'employee' in the real data, so there shouldn't be too
> much extra data sent if the processing were to be done in the client.
>
> Also if there is a better way to organize the data to achieve this I'd be
> very interested in it.
>
> This is probably a somewhat known query, but I tried googling this group
> and couldn't come up with any solutions. I didn't know quite what keywords
> to search for though which didn't help much.
>
> Regards and TIA
> -Tomas
>
>

Relevant Pages

  • Re: Error on Trigger Launch
    ... I needed to move the "SET NOCOUNT ON" below the as ... trigger, ... DECLARE @tableHTML NVARCHAR; ... Fetch Next FROM email_curs into @email ...
    (comp.databases.ms-sqlserver)
  • How do I user cursor in a stored procedure in SQl SErver?
    ... SET NOCOUNT ON ... DECLARE invoice_cursor CURSOR FOR ... FETCH NEXT FROM invoice_cursor ...
    (microsoft.public.sqlserver.programming)
  • Re: Sql script fails
    ... DECLARE @DBNAME VARCHAR ... FETCH NEXT FROM BACKUP_CUR INTO @DBNAME ... > SET NOCOUNT ON ...
    (microsoft.public.sqlserver.security)
  • Sql script fails
    ... SET NOCOUNT ON ... DECLARE @DBNAME VARCHAR ... FETCH NEXT FROM BACKUP_CUR INTO @DBNAME ...
    (microsoft.public.sqlserver.security)
  • RE: SQL 2000 instance showing up as version6.5 after running fixre
    ... exec sp_configure N'allow updates', 1 ... DECLARE @username varchar ... FETCH NEXT FROM list_triggers INTO @name, ... create table syssubscriptions (artid int, srvid smallint, dest_db sysname, ...
    (microsoft.public.sqlserver.clustering)