Concatenating a one-to-many relationship into a single column

I have seen this question asked a number of times including by myself.

The question is how do you consolidate the data from a one to many relationship into a single column with the individual values on the many side concatenated together using commas.

e.g

To convert

Deal Competitor
1 X
1 Y
1 Z
2 X
3 Y
4 Y
4 Z

To

Deal Competitors
1 X,Y,Z
2 X
3 Y
4 Y,Z


Here are two solutions

Firstly via a cursor contributed by bperry

--------------------------

(Keyval = Deal, Line = Competitor)

--------------------------
Begin Cursor
--------------------------
CREATE TABLE #TempTable (
KeyVal int,
Line varchar(200)
)

-- Create a forward-only cursor to collect the data
declare myCursor INSENSITIVE CURSOR FOR
Select Keyval, Line
From TableA
Order By KeyVal, SeqNum
Open myCursor

-- Create the local variables needed
declare @currKey int
declare @prevKey int
declare @currLine varchar(20)
declare @BigLine varchar(200)
set @currLine = ''
set @bigLine = ''
set @prevKey = 0
set @prevKey = -1 -- Prevent Insert on 1st record

-- Fetch the cursor
Fetch myCursor into @currKey, @currLine

While @@fetch_status = 0
BEGIN
If @currKey <> @prevKey
If @prevKey != -1
Begin
INSERT #TempTable
VALUES(@prevKey, @bigLine)
SET @bigLine = ''
End
set @prevKey = @currKey
set @bigLine = LTRIM(@bigline + ' ') + RTRIM(@currLine)
set
Fetch myCursor into @currKey, @currLine
END

-- Do final Insert for the last record
INSERT #TempTable VALUES(@prevKey, @bigLine)

-- Release cursor
close myCursor
deallocate myCursor

-- Report the temporary table
select KeyVal, Line From #TempTable
drop table #TempTable
-------------------------------------


Also by using a loop originally contributed by tbroadbent

keyval = Deal
CombinedLine = Competitors
-----------------------
Create table #tmp(
keyval int,
CombinedLine varchar(8000)
)

Insert #tmp
Select Distinct Keyval,''
From tableb

Declare @ln int, @mln int
Select @ln=1, @mln=Max(LineNum)
From tableB

While @ln<=@mln
Begin
Update #tmp
Set CombinedLine=CombinedLine+b.Line+' '
From #tmp a
Join tableB b
ON a.keyval=b.keyval
Where b.LineNum=@ln

Select @ln=@ln+1
End

Select * from #tmp
Drop table #tmp
go
------------------