Posted by Bart op de grote markt on 11/23/07 12:56

On 23 nov, 12:52, jhofm...@googlemail.com wrote:
> 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

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

• Next in forum: Re: foreign keys - on which kind of keys do the base on?
• Prev in forum: Re: SQL neewbie - looking for windows front-end app for SQL database backend
• Thread view: Re: Concat instead of SUM when grouping results

[Reply to this message]