INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

Concatenating a one-to-many relationship into a single column by AWithers
Posted: 8 Jul 02 (Edited 9 Jul 02)

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
------------------

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close