May 28, 2008 at 1:50 pm
I have data that looks like this:
ColA ColB
A 56432
A 65557
B 63492
B 77779
B 85000
I need the result set to look like this
ColA ColB
A 56432, 65557
B 63492, 77779, 85000
I have looked all over the internet for a way to do this. I've found a way to concatenate column B using coalesce, but I need to include column A so that I can join the results to other tables. Thanks in advance.
May 28, 2008 at 2:02 pm
Search this site or this forum for "Concatenate function"
_____________
Code for TallyGenerator
May 28, 2008 at 2:14 pm
I searched the forums with one result, that didn't address what I need.
May 28, 2008 at 2:17 pm
Start here:
http://qa.sqlservercentral.com/articles/Test+Data/61572/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 28, 2008 at 2:45 pm
Strange, when I tried to put "Concatenation function" into "search" field I've got this:
http://qa.sqlservercentral.com/Forums/Topic465637-149-1.aspx
You need some practice in using Internet search.
_____________
Code for TallyGenerator
May 28, 2008 at 3:03 pm
Sergiy (5/28/2008)
Strange, when I tried to put "Concatenation function" into "search" field I've got this:http://qa.sqlservercentral.com/Forums/Topic465637-149-1.aspx
You need some practice in using Internet search.
I appreciate your help, but take a pill. I searched in the T-SQL forum, in all forums and in the search in the menu at the top right of the screen, using keywords "Concatenation function", and got nothing.
May 28, 2008 at 4:00 pm
June 8, 2008 at 8:51 am
Ann M (5/28/2008)
I have data that looks like this:ColA ColB
A 56432
A 65557
B 63492
B 77779
B 85000
I need the result set to look like this
ColA ColB
A 56432, 65557
B 63492, 77779, 85000
I have looked all over the internet for a way to do this. I've found a way to concatenate column B using coalesce, but I need to include column A so that I can join the results to other tables. Thanks in advance.
can you create a temporal table with the result? in that case you can do this:
-- DROP TABLE #xx
-- DROP TABLE #grtable
SELECT ColA = 'A', ColB = 1
into #xx
UNION
SELECT ColA = 'A', ColB = 2
UNION
SELECT ColA = 'A', ColB = 3
UNION
SELECT ColA = 'A', ColB = 4
UNION
SELECT ColA = 'B', ColB = 5
UNION
SELECT ColA = 'B', ColB = 6
UNION
SELECT ColA = 'B', ColB = 7
SELECT * FROM #xx
DECLARE @ColA varchar(10)
DECLARE @concat VARCHAR(4000)
SELECT @Concat = ''
select colA, colBconcat = CONVERT(VARCHAR(4000),NULL) INTO #grtable from #xx group by colA
DECLARE _cursor CURSOR FOR
select colA from #grtable
OPEN _cursor
FETCH NEXT FROM _cursor INTO @ColA
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @concat = @concat + CONVERT(VARCHAR,ColB)+', ' FROM #xx WHERE ColA = @ColA
UPDATE #grtable SET ColBconcat = @Concat WHERE ColA = @ColA
FETCH NEXT FROM _cursor INTO @ColA
END
CLOSE _cursor
DEALLOCATE _cursor
SELECT * FROM #grtable
June 10, 2008 at 6:27 pm
Hi there
try this - it might not be exactly what you're looking for, but the concepts could probably be amended to suit your needs. I made a test table and populated it with the example values that you provided. Hope it is of some help, if not the complete solution to your problem 🙂
DECLARE @string VARCHAR(20), @id INT
SET @string = ''
SET @id=65 -- start at A (CHAR(65) in ascii)
PRINT 'colA' + CHAR(9) + 'colB'
WHILE @id <67-- change to suit. ascii CHAR(65) to CHAR(90) = A to Z uppercase
BEGIN
SET @string = ''
SELECT @string = @string + CAST(colB AS VARCHAR(10)) + ','
FROM test_table -- change to suit name of table
WHERE colA = CHAR(@id)
ORDER BY colA
SET @string = LEFT(@string,LEN(@string)-1) -- omit trailing comma
print CHAR(@id) + CHAR(9) + CHAR(9) + @string --use tabs for alignment
SET @id = @id +1-- increment for loop thru alphabet
END
June 10, 2008 at 7:23 pm
Did you ever find the answer to this problem? I was going to post the same link that Matt did. The answer is definitely there.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 11, 2008 at 6:30 am
No - I haven't found the answer. But, only because job requirements have taken me in another direction. Putting out fires. But, I will return to this issue once the fire fight has been won.
June 13, 2008 at 1:28 am
I have faced same problem once and i found this solution. I m taking same table for example as shown by u in ur post.
create table test
(
colA varchar(5),
colB varchar(8)
)
--I have inserted same records in the test table as u have shown.
select * into test_temp from test where 1=2
alter table test_temp alter column colB varchar(500)
create table #temp(id1 int identity,col_A varchar(76))
insert into #temp(col_A)
select distinct a.colA from test a where (select count(b.colA) from test b where a.colA=b.colA)>1
--select *from #temp
declare @a varchar(8000)
declare @b-2 varchar(1000)
DECLARE @contid VARCHAR(76)
declare @count int
declare @maxcount int
select @maxcount=max(id1) from #temp
set @count=1
while @count<=@maxcount
BEGIN
declare c1 cursor for
select cast(colB as varchar(500)) from test
where colA=(select col_A from #temp where id1=@count)
set @a=''
open c1
fetch next from c1 into @b-2
while @@fetch_status=0
begin
if @a=''
set @a=@b
else
set @a=@a+','+@b
fetch next from c1 into @b-2
end
select @contid=col_A from #temp where id1=@count
insert into test_temp(colA,colB) values(@contid,@a)
CLOSE c1
DEALLOCATE c1
set @count=@count+1
END
INSERT INTO test_temp (colA,colB)
select colA,colB from Test where colA not in(select col_A from #temp) or colA is null
drop table #temp
select * from test_temp
I hope this will solve ur problem
Shailesh
DBA (SQL server)
October 6, 2009 at 1:25 pm
SELECT ColA = 'A'
into dbo.tbl_pk
UNION
SELECT ColA = 'B'
SELECT ColA = 'A', ColB = '1'
into dbo.tbl_fk
UNION
SELECT ColA = 'A', ColB = '2'
UNION
SELECT ColA = 'A', ColB = '3'
UNION
SELECT ColA = 'A', ColB = '4'
UNION
SELECT ColA = 'B', ColB = '5'
UNION
SELECT ColA = 'B', ColB = '6'
UNION
SELECT ColA = 'B', ColB = '7'
create function dbo.udf_ColBToString
(@ColA char(1))
returns varchar(1000)
as
begin
declare @STR varchar(500)
set @STR = ''
select @STR = @STR + ', ' + fk.ColB
FROM
dbo.tbl_pk pk inner join
dbo.tbl_fk fk on
pk.ColA = fk.ColA and
pk.ColA = @ColA
return substring(@str, 2, 999)
end
select * from dbo.tbl_pk
select * from dbo.tbl_fk
select ColA, dbo.udf_ColBToString(ColA) ColBConcat from dbo.tbl_pk
[font="Courier New"]ZenDada[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply