SELECT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT

Author

Topic

DENIZ3E
Yak Posting Veteran

56 Posts

Posted - 2005-04-18 : 17:32:39

PLAN TEACHER
----------- ---------

TRAININGID TEACHERID ID NAME
-- ---- ----- ------
1 1 1 A
2 1 2 B
2 2 3 C
2 3

TRAINING
-----
ID CODE
---- -----
2 LANGUAGE
1 SPORTS

I WANT TO..

CODE TEACHER
------ --------
LANGUAGE A,B,C
SPORTS A

PLEASE HELP...

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-18 : 20:02:27

bear with me, I don't have query analyzer in front of me, but I'll try to guide you through from memory...

ok step one - get the data you want from the join and place it into a temporary table - the temp table needs to contain an extra field (called list) which is where we can build up the comma separated list of A,B,C


create #temp (code varchar(50), name varchar(50), list varchar(1000))

insert into #temp (code, name)
select a.code, b.name
from [plan teacher] a inner join [training] b 
on a.trainingid = b.id
order by a.code, b.name

step two - create a csv column for each training code

declare @sList nvarchar(1000)
Declare @sLast nvarchar(50)
set @sList = ''
set @sLast =''

update #temp 
set @sList = list = case when @sLast <> code then name else @sList + ',' + name end, @sLast = code

select code, max(list) as [teacher]
from #temp
group by code

drop table #a

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Billpl
Yak Posting Veteran

71 Posts

Posted - 2005-04-19 : 03:58:08

same thing only different:
---------------------------------------------------
Create table teacher ( Tid int, [name] char(1) )

Insert teacher select 1, 'A'
Insert teacher select 2, 'B'
Insert teacher select 3, 'C'

Create table training ( Nid int, code char(10) )

Insert training select 1, 'Sports'
Insert training select 2, 'Lanuage'

Create table assign ( Nid int, Tid int )

Insert assign select 1,1
Insert assign select 2,1
Insert assign select 2,2
Insert assign select 2,3

go
-------------------------------------
Create function dbo.teacher_grp (@Nid Int)

Returns VarChar(50) as
Begin
Declare @plan Table (

Nid int,
name char(1),
teachers Varchar(50)
)

Insert @plan Select a.Nid, b.name,' ' from assign a, teacher b where a.Nid = @Nid and a.Tid = b.Tid
Declare @list as varchar(50)
Select @list = ''
Update @plan set @list = teachers = @list + name + ' '
Return @list
End
-------------------------------------
go

Select Code, dbo.teacher_grp( Nid ) as Teachers
from training

drop function dbo.teacher_grp
drop table assign
drop table training
drop table teacher
--------------------------------------

DENIZ3E
Yak Posting Veteran

56 Posts

Posted - 2005-04-19 : 10:50:43

VERY THANKS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources