469,923 Members | 1,775 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,923 developers. It's quick & easy.

Converting multiple values in form of comma separated string

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
2 2466
azimmer
200 Expert 100+
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
kuchel
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.

Similar topics

5 posts views Thread by dnsstaiger | last post: by
32 posts views Thread by tshad | last post: by
11 posts views Thread by muttu2244 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.