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
>