435,543 Members | 2,116 Online
Need help? Post your question and get tips & solutions from a community of 435,543 IT Pros & Developers. It's quick & easy.

# Concat instead of SUM when grouping results

 P: n/a 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 Nov 23 '07 #1
11 Replies

 P: n/a 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 Nov 23 '07 #2

 P: n/a 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 Nov 23 '07 #3

 P: n/a 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 Nov 23 '07 #4

 P: n/a Hi Bart, What spec server are you using? I can run either script in under a second :-/ J Nov 23 '07 #5

 P: n/a 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 Nov 23 '07 #6

 P: n/a >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. Nov 25 '07 #7

 P: n/a On 25 nov, 19:59, --CELKO--

 P: n/a "Bart op de grote markt" I guess there is no function to do this... What is the simplestsolution? << 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 Nov 26 '07 #9

 P: n/a 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 Nov 26 '07 #10

 P: n/a On 26 nov, 14:09, "Greg D. Moore \(Strider\)"

 P: n/a 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] Nov 26 '07 #12

### This discussion thread is closed

Replies have been disabled for this discussion.