Loop through code/value pairs with one to many to concatenate the many 1

[pms18066]

I have a set of codes with text descriptions that relate to the code. This description has the potential to have multiple values in the table. ie when I do a select distinct code, value I get multiple text descriptions for each code.

I want to loop through the records and combine the descriptions into one field seperated by commas. Im not sure if a loop is the best option or if a cursor would be best. I need to know the record counts to loop through all of the distinct records, but I also need to "break" on the code changes so that I can end up with one record for each code, with a second column containing all of the descriptions seperated by commas.

SELECT Distinct
LEFT(S1.CourseCode,4) as Coursecode
Into #tmpCourseCodes
FROM dbo.SASICourse S1 (nolock)


Select Distinct
S1.Coursecode,
S2.Coursetitle as Title2005
into #tmp_w_2005
FROM #tmpCourseCodes S1 (NOLOCK)
Left Outer Join dbo.SASICourse S2 (Nolock)
on Left(S2.Coursecode,4) = Left(S1.Coursecode,4)
and S2.DistrictCode = '3201'
and S2.Schoolyear = '2005'
and S2.QuarterlyUpdateID = '180'

-- 2078 rows

-- select * from #tmp_w_2005 order by coursecode
-- drop table #tmp_w_2005

declare @tab2005 table (id int identity , CourseCode int, Title2005 varchar(8000))

Insert into @tab2005
select courseCode, CourseTitle from #tmp_w_2005

SELECT @2005DescriptionCount = @@ROWCOUNT

WHILE @2005DescriptionCount > 0
BEGIN
Select @CurrentCourseDescription = CourseTitle from #tmp_w_2005 WHERE i = @2005DescriptionCount
@FullListOfDescriptions = @FullListOfDescriptions + ',' + @CurrentCourseDescription
-- need to "break" on code
-- While Coursecode = coursecode
-- Insert into final table???
--- confusion lies here
--- Help needed

SET @2005DescriptionCount = @2005DescriptionCount -1


END

 

[gmmastros]

It sounds like you want to do something similar to this:
thread183-1159740

Take a look at that thread. If it does not completely answer your question, then post followup questions here.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[roryred]

PMS18066, How did you solves your problem in thread768-1235036

I'm having the same problem.

 

[pms18066]

Roryed...it was a temp folder getting filled up issue... once we set up a job to clear out the folder every so often it has not re occured..