Concat instead of SUM when grouping results

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

Re: Concat instead of SUM when grouping results

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

Re: Concat instead of SUM when grouping results

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

Re: Concat instead of SUM when grouping results

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

Re: Concat instead of SUM when grouping results

Hi Bart,

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

J

Re: Concat instead of SUM when grouping results

On 23 nov, 15:46, jhofm...@googlemail.com wrote:
> Hi Bart,
>
> What spec server are you using? I can run either script in under a
> second :-/
>
> J

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

Re: Concat instead of SUM when grouping results

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

>I have also tried out the solution below

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

Re: Concat instead of SUM when grouping results

>> I guess there is no function to do this... What is the simplest solution? <<

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

Re: Concat instead of SUM when grouping results

On 23 nov, 22:50, Hugo Kornelis
wrote:
> On Fri, 23 Nov 2007 05:52:13 -0800 (PST), Bart op de grote markt wrote:
>
> >I have also tried out the solution below
>
> 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

Ok,

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


Kind regards,

Bart

Re: Concat instead of SUM when grouping results

On 25 nov, 19:59, --CELKO-- wrote:
> >> I guess there is no function to do this... What is the simplest solution? <<
>
> Do it in the front end instead violating 1NF in the Database side.

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

Re: Concat instead of SUM when grouping results

"Bart op de grote markt" wrote in message
news:3e7b897e-7ff7-436f-9291-adc2ab732c32@s36g2000prg.google groups.com...
> On 25 nov, 19:59, --CELKO-- wrote:
>> >> I guess there is no function to do this... What is the simplest
>> >> solution? <<
>>
>> Do it in the front end instead violating 1NF in the Database side.
>
> 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.

You're "wrong".

You can't really separate the two. That's like saying that wheels on a car
have to do with the design, not with the actual driving.

If you design your database properly, your queries follow from that.


>
> 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.
>

Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?


>
> Kind regards,
>
> Bart



--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Re: Concat instead of SUM when grouping results

> If you design your database properly, your queries follow from that.

This is nice in theory, but in practice I have seen many occasions
where reporting requirements simply don't align with the database
(which you often have no control over and may have been designed for
an input system for example). Short of designing a new database and
ETL'ing your data across (which there certainly is a market for but in
a lot of cases would be overkill to meet a single requirement),
sometimes you have to write "non-standard" queries.

> Then do it in a middle layer. What happens when your DB changes for other
> reasons but your reports aren't supposed to?

Why would a stored procedure not qualify as a middle layer? It
provides a convenient interface between the front-end and the database
and still allows the use of this type of query which, in my opinion,
is neat and easy to implement in SQL. Does it matter if your entire
data structure underneath the stored proc changes as long as the proc
continues to serve up the same results?

J

Re: Concat instead of SUM when grouping results

On 26 nov, 14:09, "Greg D. Moore \(Strider\)"
wrote:
> "Bart op de grote markt" wrote in messagenews:3e7b897e-7ff7-436f-9291-adc2ab732c32@s36g2000prg .googlegroups.com...
>
> > On 25 nov, 19:59, --CELKO-- wrote:
> >> >> I guess there is no function to do this... What is the simplest
> >> >> solution? <<
>
> >> Do it in the front end instead violating 1NF in the Database side.
>
> > 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.
>
> You're "wrong".
>
> You can't really separate the two. That's like saying that wheels on a car
> have to do with the design, not with the actual driving.
>
> If you design your database properly, your queries follow from that.

I have not said that database design has nothing to do with querying a
database... But a query of a database is combining the available data
to hava a certain result. Putting the normal forms into your database
is a way to avoid data loss in your database when you update or delete
your data. If I query a database for a report, then the result won't
interfere with the database itself, it just gives a view on your data.
I don't want to be offensive or so, but I'm not convinced yet.

And ok, I did not design the database... it is a database from a new
application my company bought. (In fact it's about two databases from
two different applications that have to be linked in a report, but I
won't go too far to explain that :-) )

> > 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.
>
> Then do it in a middle layer. What happens when your DB changes for other
> reasons but your reports aren't supposed to?
>

As has been said by J above, the Stored Procedure acts as middle layer
between the database and the reports. If there is an update of the
database (e.g. new product version), I will adapt the stored
procedure, so that the user doesn't even notice that anything has
changed.


Kind regards and thx for all your comments

Bart

Re: Concat instead of SUM when grouping results

> You're "wrong".

Actually Greg - You're "wrong".

SQL Server is a data engine and not just a relational data storage method.

There are lots and lots of extensions and features in SQL Server to help us
gain more performance, more simplicity instead of having to code stuff in
the middle tier all the time.

For instance, if I was writing a data export why on earth would I want to
use a second programming langauge that adds complexity when I can easily use
the functions and features in T-SQL.

There is a move more to putting business logic in the data engine rather
than just using the data engine as a put and get object - see research by
Jim Gray.

> Then do it in a middle layer. What happens when your DB changes for other
> reasons but your reports aren't supposed to?

It would be a bigger change if you had done it in the middle tier - both the
data access queries would change AND the middle tier source code. That's a
lot more testing, development - it's higher risk, more complicated etc...

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]