challenging query
-
Hi friends
I need some idea on following situation.i am wondering how achieve what i wanted.
I have 2 tables Task and taskgroup.
task is PK table and taskgroup is FK table.(1-many relation)
Task table has following data
taskid date
----- ------
1 12/1/01
2 13/4/02
and taskgroup has following data
fk_taskid staffname
-------- ---------
1 Ben
1 Derek
2 rajani
2 raj
i want to write a query which returns data inthe following format
taskid date staffnames
----- ------ ---------
1 12/1/01 ben,Derek
2 13/4/02 rajani,raj
how do i achieve this ?
-
Look at the following link to give you some ideas...
http://www.sqlservercentral.com/scripts/contributions/506.asp
Cheers,
Kevin -
Hi friend
Thanks for your link.
i wrote a query like following
select taskid, date,[staff]=staffname+','+staffname
from task,taskgroup
where task.taskid=taskgroup.fk_taskid
group by taskid,entrydt,staffname
but it gives wrong data like
taskid date staffnames
----- ------ ---------
1 12/1/01 ben,ben
1 12/1/01 Derek,Derek
2 13/4/02 rajani,rajani
2 13/4/02 raj,raj
but i want it like following
taskid date staffnames
----- ------ ---------
1 12/1/01 ben,Derek
2 13/4/02 rajani,raj
may be i missing something here
-
OK, you will need to put this sql into a stored proc...
Also if you are using SQL2000, I'd use table variables instead of temporary tables.
create table #Output (TaskId int, StaffNames varchar(1000))
create table #UniqueTasks (RowIndex int identity(1,1), TaskId int)
insert into #UniqueTasks (TaskId) select distinct fk_taskid from taskgroup
declare @RowCount int, @RowIndex int, @StaffNames varchar(1000), @TaskId int
set @RowIndex = 1
set @RowCount = 1
while @RowCount > 0
begin
set @StaffNames =''
select @TaskId = TaskId, @StaffNames = @StaffNames + staffname + ', '
from taskgroup tg inner join #UniqueTasks UT on UT.Taskid = TG.fk_taskid
where RowIndex = @RowIndex
order by staffname
select @RowCount = @@RowCount, @RowIndex = @RowIndex + 1
if @RowCount > 0
begin
select @StaffNames = substring(@StaffNames, 1, len(@StaffNames)-1)
insert into #Output values (@TaskId,@StaffNames)
end
end
select
T.TaskId,
T.[Date],
X.StaffNames
from
Task T
inner join #Output X on X.TaskId = T.TaskId
drop table #UniqueTasks)
drop table #Output
Cheers,
Kevin -
Thank you very much Kevin
It worked beautifully
now i have to implement it in my actual query.
thanks for your valuable time.
cheers
rajani
-
Hi, only if you working with sql 2000:
1 First of all, you need create a function:
Create function dbo.staffnames (@taskid int)
RETURNS nvarchar(255) AS
BEGIN
declare @txt nvarchar(255)
set @txt = ''
select @txt = @txt +','+ g.staffname from taskgroup g where g.taskid = @taskid
set @txt = substring(@txt,2,255)
return @txt
END
2.- and finally here your code:
select taskid , dbo.staffnames(taskid) as StaffNames from task
------------------
As you can see, you don`t need temporary tables. Temporary tables consume resources since you need to write on disk.
:.::.:.::
-
cool my friend
Yes i use sql 2000.I'll try your Idea.
Thank you so much.
-
I just tried your solution.what can i say
it worked like a charm.good thing is it was so
easy to implement your idea.
Thank you so much replying to my question even
after it's been answered.
Keep it up my friend
-
User defined function. Now why didn't I think of that! That's a much more elegant solution, go with that.
Good work.
Cheers,
Kevin
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply