SQL Server Performance Forum – Threads Archive

Grouping and concatenating string

I have a table like this declare @tbl table (
a int ,
b char(1)
)
insert into @tbl values (1,’a’)
insert into @tbl values (1,’b’)
insert into @tbl values (1,’c’)
insert into @tbl values (2,’d’)
insert into @tbl values (2,’e’)
insert into @tbl values (3,’a’) select * from @tbl a b
=========
1a
1b
1c
2d
2e
3a
I wanna concatenate all the string, like this declare @str varchar(8000)
set @str = ” select @str = @str + ‘,’ + b
from @tbl
where a = 1 So i get ‘,a,b,c’ I don’t care about the extra leading ,
(I can do the coaelesce trick but I really don’t care about the extra ‘,’) But I can do this for just one row at a time. What I really want is to get a TABLE like this 1 ‘,a,b,c’
2 ‘,d,e’
3 ‘,a’ But I can’t really do a group by. I can’t use an auxiliar scalar function cos I can’t pass the @tbl table variable to the function. The only thing I can think of is a #temp table and an auxiliary function.
but I really don’t wanna do that. Is there an easy solution?
Cleaner and more elegant? Thanx.

Either this, or create a UDF that does the concatenation, instead of a cursor … You will notice that I’ve replaced the TABLE variables with temporary tables: SQL Server was refusing to accept a table variable in the concatenation. Not sure why that is. CREATE TABLE #tbl (a int, b char(1))
CREATE TABLE #tbl2 (a int, string VARCHAR(100)) declare @hold INT, @string VARCHAR(100) insert into #tbl values (1,’a’)
insert into #tbl values (1,’b’)
insert into #tbl values (1,’c’)
insert into #tbl values (2,’d’)
insert into #tbl values (2,’e’)
insert into #tbl values (3,’a’) DECLARE Test CURSOR FORWARD_ONLY
FOR
SELECT a FROM #tbl GROUP BY a
OPEN Test FETCH NEXT FROM Test INTO @hold
WHILE @@FETCH_STATUS = 0
BEGIN
SET @string = ”
SELECT @string = @string + CASE WHEN @string = ” THEN ” ELSE ‘,’ END + #tbl.b
FROM #tbl
WHERE #tbl.a = @hold
ORDER BY #tbl.b
INSERT INTO #tbl2 VALUES (@hold, @string)
FETCH NEXT FROM Test INTO @hold
END CLOSE Test
DEALLOCATE Test SELECT * FROM #tbl2 DROP TABLE #tbl
DROP TABLE #tbl2

Thanx. But I already think of a couple of solutions with temporary tables. I was wandering if this could be acomplished WITHOUT using temp tables/cursors (cursors are _evil_, you know [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] ). So far I couldn’t think of a solution without a temp table/cursors. (the goal is use only table variables, and UDF and such)

Cursors can only be evil if you’re not careful, and also sometimes there is just no other way. Temporary tables are very helpful most of the time, and unavoidable a lot of the time. Also, as soon as a certain amount of data is expected to be stored in a table variable, SQL Server will create a working temporary table of its own accord … You may be able to use a global temporary table (CREATE TABLE ##MyTable …) and let the UDF do the concatenation routine on that. This takes out the cursor, which would be the biggest performance issue in any case. You have to remember that a database can be properly normalized, but still some reporting feature can require de-normalized output (like your comma-delimited list). If you run into a situation where you just have to make things happen, then just make it happen. Give proper warning about possible performance issues and you’re covered.

Sure. You’re absolutely right. But I just don’t like temp table (maybe because the haven’t the clear scope of table variable) and cursors. Of course, when I gotta make something work, all this "pedantic" feelings are blown away, I grab the sledgehammer and start to hammer my way through T-SQL [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] . Anyway, it’s an interesting thing to see I this could be accomplished without evil stuff [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

]]>