help with query

HI I have a table that someone else designed like
*********************************************
* employeeid * firstname * last name * supervisor_id *
*********************************************
* 1 * Paul * Sed * 2
*
*********************************************
* 2 * John * Edward * 1 *
*********************************************
I need a query that will return Johns name for Pauls supervisor
(supervisorid=2)
and Pauls name for Johns supervisor, supervisor id for Johns supervisor is 1
which is the employeeid for Paul. Thanks.
Paul G
Software engineer.
Why is it that these two people are each other's supervisor?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:924257B5-2D18-4C2F-84F5-8C9C7BAA2AEC@.microsoft.com...
HI I have a table that someone else designed like
*********************************************
* employeeid * firstname * last name * supervisor_id *
*********************************************
* 1 * Paul * Sed * 2
*
*********************************************
* 2 * John * Edward * 1 *
*********************************************
I need a query that will return Johns name for Pauls supervisor
(supervisorid=2)
and Pauls name for Johns supervisor, supervisor id for Johns supervisor is 1
which is the employeeid for Paul. Thanks.
Paul G
Software engineer.
|||select t.FirstName,t.LastName,b.FirstName as 'Supervisor FirstName',
b.LastName as 'Supervisor FirstName' from
#temp t join #temp b
on b.employeeid=t.supervisor_id
#temp =Table Name
Sam
"Tom Moreau" wrote:

> Why is it that these two people are each other's supervisor?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:924257B5-2D18-4C2F-84F5-8C9C7BAA2AEC@.microsoft.com...
> HI I have a table that someone else designed like
> *********************************************
> * employeeid * firstname * last name * supervisor_id *
> *********************************************
> * 1 * Paul * Sed * 2
> *
> *********************************************
> * 2 * John * Edward * 1 *
> *********************************************
> I need a query that will return Johns name for Pauls supervisor
> (supervisorid=2)
> and Pauls name for Johns supervisor, supervisor id for Johns supervisor is 1
> which is the employeeid for Paul. Thanks.
> --
> Paul G
> Software engineer.
>
|||this is actually not the case, just a simplified example.
Paul G
Software engineer.
"Tom Moreau" wrote:

> Why is it that these two people are each other's supervisor?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:924257B5-2D18-4C2F-84F5-8C9C7BAA2AEC@.microsoft.com...
> HI I have a table that someone else designed like
> *********************************************
> * employeeid * firstname * last name * supervisor_id *
> *********************************************
> * 1 * Paul * Sed * 2
> *
> *********************************************
> * 2 * John * Edward * 1 *
> *********************************************
> I need a query that will return Johns name for Pauls supervisor
> (supervisorid=2)
> and Pauls name for Johns supervisor, supervisor id for Johns supervisor is 1
> which is the employeeid for Paul. Thanks.
> --
> Paul G
> Software engineer.
>
|||ok thanks I think that is what I was looking for.
Paul G
Software engineer.
"bluefish" wrote:
[vbcol=seagreen]
> select t.FirstName,t.LastName,b.FirstName as 'Supervisor FirstName',
> b.LastName as 'Supervisor FirstName' from
> #temp t join #temp b
> on b.employeeid=t.supervisor_id
> #temp =Table Name
> Sam
>
> "Tom Moreau" wrote:

help with query

HI I have a table that someone else designed like
*********************************************
* employeeid * firstname * last name * supervisor_id *
*********************************************
* 1 * Paul * Sed * 2
*
*********************************************
* 2 * John * Edward * 1 *
*********************************************
I need a query that will return Johns name for Pauls supervisor
(supervisorid=2)
and Pauls name for Johns supervisor, supervisor id for Johns supervisor is 1
which is the employeeid for Paul. Thanks.
--
Paul G
Software engineer.Why is it that these two people are each other's supervisor?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:924257B5-2D18-4C2F-84F5-8C9C7BAA2AEC@.microsoft.com...
HI I have a table that someone else designed like
*********************************************
* employeeid * firstname * last name * supervisor_id *
*********************************************
* 1 * Paul * Sed * 2
*
*********************************************
* 2 * John * Edward * 1 *
*********************************************
I need a query that will return Johns name for Pauls supervisor
(supervisorid=2)
and Pauls name for Johns supervisor, supervisor id for Johns supervisor is 1
which is the employeeid for Paul. Thanks.
--
Paul G
Software engineer.|||select t.FirstName,t.LastName,b.FirstName as 'Supervisor FirstName',
b.LastName as 'Supervisor FirstName' from
#temp t join #temp b
on b.employeeid=t.supervisor_id
#temp =Table Name
Sam
"Tom Moreau" wrote:
> Why is it that these two people are each other's supervisor?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:924257B5-2D18-4C2F-84F5-8C9C7BAA2AEC@.microsoft.com...
> HI I have a table that someone else designed like
> *********************************************
> * employeeid * firstname * last name * supervisor_id *
> *********************************************
> * 1 * Paul * Sed * 2
> *
> *********************************************
> * 2 * John * Edward * 1 *
> *********************************************
> I need a query that will return Johns name for Pauls supervisor
> (supervisorid=2)
> and Pauls name for Johns supervisor, supervisor id for Johns supervisor is 1
> which is the employeeid for Paul. Thanks.
> --
> Paul G
> Software engineer.
>|||this is actually not the case, just a simplified example.
--
Paul G
Software engineer.
"Tom Moreau" wrote:
> Why is it that these two people are each other's supervisor?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:924257B5-2D18-4C2F-84F5-8C9C7BAA2AEC@.microsoft.com...
> HI I have a table that someone else designed like
> *********************************************
> * employeeid * firstname * last name * supervisor_id *
> *********************************************
> * 1 * Paul * Sed * 2
> *
> *********************************************
> * 2 * John * Edward * 1 *
> *********************************************
> I need a query that will return Johns name for Pauls supervisor
> (supervisorid=2)
> and Pauls name for Johns supervisor, supervisor id for Johns supervisor is 1
> which is the employeeid for Paul. Thanks.
> --
> Paul G
> Software engineer.
>|||ok thanks I think that is what I was looking for.
--
Paul G
Software engineer.
"bluefish" wrote:
> select t.FirstName,t.LastName,b.FirstName as 'Supervisor FirstName',
> b.LastName as 'Supervisor FirstName' from
> #temp t join #temp b
> on b.employeeid=t.supervisor_id
> #temp =Table Name
> Sam
>
> "Tom Moreau" wrote:
> > Why is it that these two people are each other's supervisor?
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > ..
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:924257B5-2D18-4C2F-84F5-8C9C7BAA2AEC@.microsoft.com...
> > HI I have a table that someone else designed like
> > *********************************************
> > * employeeid * firstname * last name * supervisor_id *
> > *********************************************
> > * 1 * Paul * Sed * 2
> > *
> > *********************************************
> > * 2 * John * Edward * 1 *
> > *********************************************
> > I need a query that will return Johns name for Pauls supervisor
> > (supervisorid=2)
> > and Pauls name for Johns supervisor, supervisor id for Johns supervisor is 1
> > which is the employeeid for Paul. Thanks.
> >
> > --
> > Paul G
> > Software engineer.
> >
> >

HELP with query

I have the following records:
Date EmployeeId Project
20050503 12345 VERIZON
20050503 12345 CINGULAR
20050503 12345 SPRINT
20050503 24680 CINGULAR
I need the resulting table to look like this:
Date EmployeeId Project
20050503 12345 VERIZON, CINGULAR, SPRINT
20050503 24680 CINGULAR
How do I separate the projects with a comma?
Thanks,
Ninel
Message posted via http://www.webservertalk.comYou have t oconstruct the comma delimited string, one value at a time. there
are a number of ways to implement this, using Cursorss, Temp tables, table
variables, or a Loop in SQL Code... Following demonstrates the latter...
Declare @.Out Table(DT DateTime, EmpID Int,
LastProject VarChar(100) default '',
Project VarChar(3000) Default '')
Insert @.Out (DT, EmpID)
Select Distinct Date, EmployeeID
From Table
-- ---
Declare @.Proj VarChar(100)
While Exists (Select * From @.Out O
Join Table T On T.Date = O.DT
And T.EmployeeID = O.EmpID
And T.Project > O.LastProject)
Update @.Out Set
LastProject =
(Select Min(Project) From Table
Where Date = @.Out.DT
And EmployeeID = @.Out.EmpID
And Project > @.Out.LastProject),
Project = Project + ', ' +
(Select Min(Project) From Table
Where Date = @.Out.DT
And EmployeeID = @.Out.EmpID
And Project > @.Out.LastProject)
Select DT, EmpID, Project From @.Out
-- ---
"ninel gorbunov via webservertalk.com" wrote:

> I have the following records:
> Date EmployeeId Project
> 20050503 12345 VERIZON
> 20050503 12345 CINGULAR
> 20050503 12345 SPRINT
> 20050503 24680 CINGULAR
> I need the resulting table to look like this:
> Date EmployeeId Project
> 20050503 12345 VERIZON, CINGULAR, SPRINT
> 20050503 24680 CINGULAR
> How do I separate the projects with a comma?
> Thanks,
> Ninel
> --
> Message posted via http://www.webservertalk.com
>|||Last Post had error, try this...
Declare @.Out Table(DT DateTime, EmpID Int,
LastProject VarChar(100) default '',
Project VarChar(3000) Default '')
Insert @.Out (DT, EmpID)
Select Distinct Date, EmployeeID
From Table
-- ---
While Exists (Select * From @.Out O
Join Table T On T.Date = O.DT
And T.EmployeeID = O.EmpID
And T.Project > O.LastProject)
Update @.Out Set
LastProject =
(Select Min(Project) From Table
Where Date = @.Out.DT
And EmployeeID = @.Out.EmpID
And Project > @.Out.LastProject),
Project = Project + ', ' +
(Select Min(Project) From Table
Where Date = @.Out.DT
And EmployeeID = @.Out.EmpID
And Project > @.Out.LastProject)
Where Exists (Select * From Table
Where Date = @.Out.DT
And EmployeeID = @.Out.EmpID
And Project > @.Out.LastProject)
Select DT, EmpID, Project From @.Out
-- ---
"CBretana" wrote:
> You have t oconstruct the comma delimited string, one value at a time. the
re
> are a number of ways to implement this, using Cursorss, Temp tables, table
> variables, or a Loop in SQL Code... Following demonstrates the latter...
> Declare @.Out Table(DT DateTime, EmpID Int,
> LastProject VarChar(100) default '',
> Project VarChar(3000) Default '')
> Insert @.Out (DT, EmpID)
> Select Distinct Date, EmployeeID
> From Table
> -- ---
> Declare @.Proj VarChar(100)
> While Exists (Select * From @.Out O
> Join Table T On T.Date = O.DT
> And T.EmployeeID = O.EmpID
> And T.Project > O.LastProject)
> Update @.Out Set
> LastProject =
> (Select Min(Project) From Table
> Where Date = @.Out.DT
> And EmployeeID = @.Out.EmpID
> And Project > @.Out.LastProject),
> Project = Project + ', ' +
> (Select Min(Project) From Table
> Where Date = @.Out.DT
> And EmployeeID = @.Out.EmpID
> And Project > @.Out.LastProject)
> Select DT, EmpID, Project From @.Out
> -- ---
>
> "ninel gorbunov via webservertalk.com" wrote:
>|||The query is not working...
Do I need to copy it the way it is?
Message posted via http://www.webservertalk.com|||Hi
Do such kind of things on the client side
If you persist to do that by T-SQL see an example
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"ninel gorbunov via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:69a462676fa7409d8fba6fff71420895@.SQ
webservertalk.com...
> The query is not working...
> Do I need to copy it the way it is?
> --
> Message posted via http://www.webservertalk.com|||What I sent you was a "Block" of SQL, you have to copy all of it into a Quer
y
ANalyzer window and run it all at once, or put it into a Stored Proc and run
the Stored Proc.. . Al the statements in the block must be executed within
the same call... since the block creates variables that are used by later
statements...
"ninel gorbunov via webservertalk.com" wrote:

> The query is not working...
> Do I need to copy it the way it is?
> --
> Message posted via http://www.webservertalk.com
>

HELP with query

I have the following records:
Date EmployeeId Project
20050503 12345 VERIZON
20050503 12345 CINGULAR
20050503 12345 SPRINT
20050503 24680 CINGULAR
I need the resulting table to look like this:
Date EmployeeId Project
20050503 12345 VERIZON, CINGULAR, SPRINT
20050503 24680 CINGULAR
How do I separate the projects with a comma?
Thanks,
NinelHi
The best place to do this is on the client, but these may help
http://tinyurl.com/6rhsj
John
"ninel" wrote:

> I have the following records:
> Date EmployeeId Project
> 20050503 12345 VERIZON
> 20050503 12345 CINGULAR
> 20050503 12345 SPRINT
> 20050503 24680 CINGULAR
> I need the resulting table to look like this:
> Date EmployeeId Project
> 20050503 12345 VERIZON, CINGULAR, SPRINT
> 20050503 24680 CINGULAR
> How do I separate the projects with a comma?
> Thanks,
> Ninel
>|||create table _data
(
Date datetime,
EmployeeId int,
Project varchar(50)
)
insert into _data values('20050503', 12345, 'VERIZON')
insert into _data values('20050503', 12345, 'CINGULAR')
insert into _data values('20050503', 12345, 'SPRINT')
insert into _data values('20050503', 24680, 'CINGULAR')
create function dbo.Projects
(
@.intEmployeeId int
)
returns varchar(8000)
as
begin
declare @.vcProjects varchar(8000)
set @.vcProjects = ''
select
@.vcProjects = @.vcProjects + case when @.vcProjects = '' then '' else ', '
end + Project
from
_data (nolock)
where
EmployeeId = @.intEmployeeId
return @.vcProjects
end
select
Date, EmployeeId, dbo.Projects(EmployeeId)
from
_data (nolock)
group by
Date, EmployeeId
Hope this works.
Best Regards,
Lakshman.
"ninel" wrote:

> I have the following records:
> Date EmployeeId Project
> 20050503 12345 VERIZON
> 20050503 12345 CINGULAR
> 20050503 12345 SPRINT
> 20050503 24680 CINGULAR
> I need the resulting table to look like this:
> Date EmployeeId Project
> 20050503 12345 VERIZON, CINGULAR, SPRINT
> 20050503 24680 CINGULAR
> How do I separate the projects with a comma?
> Thanks,
> Ninel
>

help with query

HI I have a table that someone else designed like
****************************************
*****
* employeeid * firstname * last name * supervisor_id *
****************************************
*****
* 1 * Paul * Sed * 2
*
****************************************
*****
* 2 * John * Edward * 1 *
****************************************
*****
I need a query that will return Johns name for Pauls supervisor
(supervisorid=2)
and Pauls name for Johns supervisor, supervisor id for Johns supervisor is 1
which is the employeeid for Paul. Thanks.
Paul G
Software engineer.Why is it that these two people are each other's supervisor?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:924257B5-2D18-4C2F-84F5-8C9C7BAA2AEC@.microsoft.com...
HI I have a table that someone else designed like
****************************************
*****
* employeeid * firstname * last name * supervisor_id *
****************************************
*****
* 1 * Paul * Sed * 2
*
****************************************
*****
* 2 * John * Edward * 1 *
****************************************
*****
I need a query that will return Johns name for Pauls supervisor
(supervisorid=2)
and Pauls name for Johns supervisor, supervisor id for Johns supervisor is 1
which is the employeeid for Paul. Thanks.
Paul G
Software engineer.|||select t.FirstName,t.LastName,b.FirstName as 'Supervisor FirstName',
b.LastName as 'Supervisor FirstName' from
#temp t join #temp b
on b.employeeid=t.supervisor_id
#temp =Table Name
Sam
"Tom Moreau" wrote:

> Why is it that these two people are each other's supervisor?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:924257B5-2D18-4C2F-84F5-8C9C7BAA2AEC@.microsoft.com...
> HI I have a table that someone else designed like
> ****************************************
*****
> * employeeid * firstname * last name * supervisor_id *
> ****************************************
*****
> * 1 * Paul * Sed * 2
> *
> ****************************************
*****
> * 2 * John * Edward * 1
*
> ****************************************
*****
> I need a query that will return Johns name for Pauls supervisor
> (supervisorid=2)
> and Pauls name for Johns supervisor, supervisor id for Johns supervisor is
1
> which is the employeeid for Paul. Thanks.
> --
> Paul G
> Software engineer.
>|||this is actually not the case, just a simplified example.
--
Paul G
Software engineer.
"Tom Moreau" wrote:

> Why is it that these two people are each other's supervisor?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:924257B5-2D18-4C2F-84F5-8C9C7BAA2AEC@.microsoft.com...
> HI I have a table that someone else designed like
> ****************************************
*****
> * employeeid * firstname * last name * supervisor_id *
> ****************************************
*****
> * 1 * Paul * Sed * 2
> *
> ****************************************
*****
> * 2 * John * Edward * 1
*
> ****************************************
*****
> I need a query that will return Johns name for Pauls supervisor
> (supervisorid=2)
> and Pauls name for Johns supervisor, supervisor id for Johns supervisor is
1
> which is the employeeid for Paul. Thanks.
> --
> Paul G
> Software engineer.
>|||ok thanks I think that is what I was looking for.
--
Paul G
Software engineer.
"bluefish" wrote:
[vbcol=seagreen]
> select t.FirstName,t.LastName,b.FirstName as 'Supervisor FirstName',
> b.LastName as 'Supervisor FirstName' from
> #temp t join #temp b
> on b.employeeid=t.supervisor_id
> #temp =Table Name
> Sam
>
> "Tom Moreau" wrote:
>