By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,830 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,830 IT Pros & Developers. It's quick & easy.

Converting multiple values in form of comma separated string

P: 5
Hi
I have a table which looks typically like this:-
ColA COLB
2370 2875
2370 3194
2376 3108
2417 2437
2539 2774
2539 2732
2539 3240

AS you can see from above ColB has repeated values. Fro example 2370 has 2875 and 3194 in ColB
What I am trying to do is

ColA COLB
2370 2875,3194
2376 3108
2417 2437
2539 2774,2732,3240

Can somebody guide me please
Cheers
Aug 1 '07 #1
Share this Question
Share on Google+
2 Replies


azimmer
Expert 100+
P: 200
Hi
I have a table which looks typically like this:-
ColA COLB
2370 2875
2370 3194
2376 3108
2417 2437
2539 2774
2539 2732
2539 3240

AS you can see from above ColB has repeated values. Fro example 2370 has 2875 and 3194 in ColB
What I am trying to do is

ColA COLB
2370 2875,3194
2376 3108
2417 2437
2539 2774,2732,3240

Can somebody guide me please
Cheers
Here it goes, polish if you like (input table is Test, output goes to TestOut (int,varchar(255)):
Expand|Select|Wrap|Line Numbers
  1. declare @colA as int, @newcolA as int, @colB as int
  2. declare @newcolB as varchar(255)
  3. declare crsr cursor for select colA, colB from Test order by colA
  4. open crsr
  5. fetch next from crsr into @newcolA, @colB
  6. set @colA=@newcolA-1
  7. set @newcolB=''
  8. while @@FETCH_STATUS=0
  9. begin
  10.     if (@colA = @newcolA)
  11.     begin
  12.         set @newcolB = @newcolB + ',' + cast(@colB as varchar)
  13.     end
  14.     else
  15.     begin
  16.         if (@newcolB<>'') insert into TestOut values (@colA,@newcolB)
  17.         set @newcolB = cast(@colB as varchar)
  18.         set @colA = @newcolA
  19.     end
  20.     fetch next from crsr into @newcolA, @colB
  21. end
  22. if (@newcolB<>'') insert into TestOut values (@colA,@newcolB)
  23. close crsr
  24. deallocate crsr
  25.  
Aug 1 '07 #2

P: 5
thanks azimmer
I appreciate that.
Cheers,
Aug 2 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.