Need to concatenate comments and group by Order in Subquery 2

[dgillz]

So I have an order table and comments is a separate table. There could be several lines of comments per order, or there could be none. So my desired output should look like this:

Order_no, Order_date, Comments

So my SQL is straight forward:

select O.ord_no, O.ord_dt, C.cmt
from oeordhdr_sql O 
    left join oelincmt_sql C on O.ord_no = C.ord_no
                         and O.ord_type = C.ord_type

However If I have 3 lines of Comments I am of course getting 3 records for that Ord_no. How do I write a subquery that concatenates the comments, rendering only 1 line per order number?

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[dhookom]

Consider checking the solutions in the FAQs of this forum. Or possibly this more comprehensive list of options.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dgillz]

None of the FAQa dealt with grouping, they all created just one big string.

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[Andrzejek]

You want "a subquery that concatenates the comments, rendering only 1 line per order number"
and you are getting "just one big string" (of comments, I assume).
Isn't it just what you wanted...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dgillz]

No. The "one big string" is for every order in the database.

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[fredericofonseca]

the example you need is on

https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation

to be more precise this one

SELECT Name, Pets = STUFF((SELECT N', ' + Pet 
  FROM dbo.FamilyMemberPets AS p2
   WHERE p2.name = p.name 
   ORDER BY Pet
   FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
FROM dbo.FamilyMemberPets AS p
GROUP BY Name
ORDER BY Name;

and this was on the links Andy supplied

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Andrzejek]

If you have SQL Server 2017 or newer, you may use STRING_AGG

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dgillz]

That did the trick.

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html