Simplify return data from join SQL

Question

I am just curious, how do I concat join table so the result doesn't return many rows?

What I want is the firstname d return only one row like 001;004;005;003;007 in column interest, so in my grid doesn't display many rows.

Here's my code

 .CommandText = String.Format("select aa.code, aa.firstname, cc.interest as interest from db.name aa ") _
 '& ("left join db.interest cc on cc.lead = aa.code ") _
 '& ("where convert(date,time) between '" & date1& "' and '" & date2& "' order by aa.code ")

I am asking here because I don't know the keyword that relates to this

MSSQL : https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — etsa, Aug 25 '17 at 10:25
please don't write your queries like this by joining strings together. Instead use proper parameterised queries. It will save you from obscure syntax errors and also (more importantly) SQL injection attacks. See http://bobby-tables.com/ to understand why your code is vulnerable, and also see some examples of how to do it properly and safely (they are in C# but you can easily convert it to VB online, or look up the same thing elsewhere) — ADyson, Aug 25 '17 at 10:32
@ADyson thanks for the concern. but now i don't thing this will got a sql injection since it's not a textbox to type instead of button. i need a keyword to solve this :) — chopperfield, Aug 25 '17 at 10:57
bad question: no well formatted sql code + redundant code on another language. but anyway, as etsa says.... https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string/545672#545672 — xdd, Aug 25 '17 at 11:08
@chopperfield it doesn't really matter how the date variables are generated, as a general rule you shouldn't trust anything coming back from the application, as it could be interfered with in some way. It's perhaps a bit less likely in a desktop app than in a web app, but you shouldn't assume that it's ok. Plus parameters will save you from other issues like unexpected syntax errors etc. — ADyson, Aug 25 '17 at 11:25
It's called Grouped string aggregation, there are already [some](https://stackoverflow.com/questions/15477743/listagg-in-sqlserver) [answers](https://stackoverflow.com/questions/3368942/grouped-string-aggregation-listagg-for-sql-server). Also, don't concatenate your queries with variables! — the_lotus, Aug 25 '17 at 13:06

score 0 · Answer 1 · answered Aug 25 '17 at 11:07

You need to add GROUP BY Steatment to your query:

 .CommandText = String.Format("select aa.code, aa.firstname,COUNT(aa.firstname) as 'FnameCount', cc.interest as interest from db.name aa ") _
 '& ("left join db.interest cc on cc.lead = aa.code ") _
 '& ("where convert(date,time) between '" & date1& "' and '" & date2& "' GROUP BY aa.code, aa.firstname, cc.interest  order by aa.code ")

Simplify return data from join SQL

1 Answers1