How to get multiple results in 1 record

I have the following query:

SELECT 
,e.EmployeeCode
,c.CompanyName
,v.Violation
FROM dbo.Employee e
INNER JOIN dbo.Company c ON c.companyid = e.companyid
INNER JOIN dbo.Violation v ON v.CompanyId = e.CompanyId AND v.EmployeeId = e.EmployeeId

It returns results like the following:

开发者_Go百科

EmployeeCode    CompanyName     Violation
     1             Test           32
     1             Test           12 
     2             ABC1           32

Row Count (3) <----Not part of the results, just showing the number of rows I have.

Is there a way to return the results as so?

EmployeeCode   CompanyName    Violation
     1              Test          32
                                  12
     2               ABC1         32

Row Count (2) <----Not part of the results, just showing the number of rows I want.

Basically I want it to show the Violations with a cariage return in the same row.

I was thinking about using the FOR XML Path, but would this work with a carriage return? And how would I even do that?

---

I would suggest to do such transformations on application level where they really makes sense for some kind of reporting stuff.

---

select e.EmployeeCode,
       c.CompanyName,
       stuff((select char(13)+char(10)+v.Violation
              from Violation as v
              where v.CompanyID = e.CompanyID and
                    v.EmployeeID = e.CompanyID
              for xml path(''), type).value('.', 'varchar(max)'), 1, 2, '') as Violation      
from Employee as e
  inner join Company as c
    on e.CompanyID = c.CompanyID

---

I concur that this may be best performed outside of the DB layer, but it's an interesting TSQL exercise and find this resource to be the most comprehensive overview of the different approaches:

https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/