Concatenating a one-to-many relationship into a sigle field 1

[AWithers]

I have a situation where I have a table of data where I have many competitors/deal e.g.

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

I want to create a query for reporting purposes which concatenates all competitors/deal together e.g.
Deal Competitors
1 X,Y,Z
2 X
3 Y
4 Y,Z

I have looked at cross-tab queries but feel I need some sort of loop for each deal to insert/update the data

Any suggestions are welcome

Andy

 

[Jonax]

I guess what you need is a stored proc with a cursor... This is not a bug - it's an undocumented feature...
;-)

 

[bperry]

Hi there,

This (nearly) same issue was raised last week. I suggested a cursor, and tbroadbent proposed a script loop.

I don't remember the thread, but I happened to save the notes on my hard drive.

The cursor looked liked this. (Keyval = Deal, Line = Competitor) I will look around to see if I can also find the script loop.

[tt]
--------------------------
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
-------------------------------------

 

[AWithers]

Thanks

I got that working very quickly

Andy

 

[bperry]

I have found the script loop (originally contributed by tbroadbent) to solve the earlier issue similar to yours.

Hope one of these solutions helped. Because of the nature of these problems (i.e. remembering the value from the previous row), I don't think there's any other way, or any nice set-based solution, to getting around this. (as Jonax mentioned above).

rgrds, etc
bperry

[tt]
-----------------------
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
------------------
[/tt]