Hello,

I have a very simple problem which I will illustrate with an example:

I have the following records in my table:
A 1 C
A 2 C
A 3 C
B 8 K
B 9 K

I now want to group them and the result has to be:
A 1,2,3 C
B 8,9 K

So the results in the second row have to be concatenated. I guess
there is no function to do this... What is the simplest solution?

Kind regards,

Bart Warnez

Hi Bart,

I've seen this question answered very neatly before, so with a bit of
digging and some copy/paste I came up with:

CREATE TABLE test (test1 VARCHAR(5), test2 varchar(5), test3
varchar(5))

INSERT INTO test(test1, test2, test3)
SELECT 'A', '1', 'C'
UNION ALL
SELECT 'A', '2', 'C'
UNION ALL
SELECT 'A', '3', 'C'
UNION ALL
SELECT 'B', '8', 'C'
UNION ALL
SELECT 'B', '9', 'C'

SELECT test1, SUBSTRING((select ', ' + test2 as [text()]
from test t
where t.test1 = ot.test1
for xml path(''), elements), 3, 100) as test2, test3
FROM test ot
GROUP BY test1, test3

DROP TABLE test

which seems to work

Good luck!
J

On 23 nov, 12:52, jhofm... (AT) googlemail (DOT) com wrote:
Hey, thank you very much, it works . The only problem is that it
lasts more than 10 s to execute it and that with only 5 records .

Kind Regards,

Bart

I have also tried out the solution below (with the same test-table),
with a function. But again the response time is very slow...

create function dbo.fn_groupIt(@test1 varchar(5),@test3 varchar(5))
returns varchar(5000)
as
begin
declare @out varchar(5000)
select @out = coalesce(@out + ',' + convert(varchar,test2),
convert(varchar,test2))
from test
where test1 = @test1 and
test3 = @test3

return @out
end

select test1, dbo.fn_groupIt(test1,test3) test2,test3
from (
select test1,test3
from test
group by test1,test3
) a

Hi Bart,

What spec server are you using? I can run either script in under a
second :-/

J

On 23 nov, 15:46, jhofm... (AT) googlemail (DOT) com wrote:
Ok, I asked for another testserver because the first one was
apparently overloaded (read: dead). I didn't notice that at first
because a simple table-select took no time at all and those other
scripts took 10-20 seconds. On the new server, it takes no time...
Yes, you are right and I am happy . Thank you very much!

Bart

On Fri, 23 Nov 2007 05:52:13 -0800 (PST), Bart op de grote markt wrote:

Hi Bart,

Don't use this one - the "trick" it uses is undocumented, so it might
stop to work in a future release or even after applying a hotfix. In
fact, there have already been situations documented where it doesn't
work as expected (I unfortunately lost the URL though).

Use the FOR XML trick that J posted instead. However, because of his use
of SUBSTRING, the maximum length for the concatenation is limited to 100
characters (or whatever fixed value you use). You can solve that by
using STUFF instead of SUBSTRING:

SELECT test1,
STUFF((SELECT ', ' + test2 AS [text()]
FROM test AS t
WHERE t.test1 = ot.test1
AND t.test3 = ot.test3
FOR XML PATH(''), ELEMENTS), 1, 2, '') AS test2,
test3
FROM test AS ot
GROUP BY test1, test3;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Do it in the front end instead violating 1NF in the Database side.

On 23 nov, 22:50, Hugo Kornelis <h... (AT) perFact (DOT) REMOVETHIS.info.INVALID>
wrote:
Ok,

Thank you for this warning and the extra comments! I will follow the
solution as suggested then .


Kind regards,

Bart

On 25 nov, 19:59, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:
Hi,

I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.

I would like most of the logic on server side, (the report result is
retrieved by an excel report that mainly adds lay-out and adds the
possibility to further process the results) because when an update of
the report is needed, I only need to change the stored procedure and
not the 'front-end' excel reports with everybody that uses it.


Kind regards,

Bart