How to combine 2 or more rows in one and concatenate one column with comma Seperator

Question

enter image description hereI want to Create a View it has some columns and all the rows have the same result except one column, how to merge or combine All the rows in a one row but one column with the different value I wanted to show them in one column and split the values with comma. if there is a way show me by Query.

CREATE VIEW [ret_vwSalaried]
 AS
SELECT 
   salaried.FirstName,
   salaried.LastName,
   salaried.IdNumber,
   salaried.PersonnelNumber,
   operation.OperationalUnit
FROM 
   ret_Salaried salaried INNER JOIN
   ret_OperationalUnitFeaturs operation ON salaried`enter code here`.Guid = operation.SalariedGuid
WHERE Deleted = 0

the result includes 3 rows, I wanted to merge them in one row because they are the same but Column [OperationalUnit] has Different result (each person could have different operational unit), and I want to merge them too, in the row and split them with comma.

Edit the question add some sample data & desired result would helpful. — Yogesh Sharma, Apr 16 '19 at 18:35
I don't understand what you want, please sample data and expected results might help a lot — Alvaro Parra, Apr 16 '19 at 18:44
Possible duplicate of [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — Alex Kudryashev, Apr 16 '19 at 19:03
Duplicate of https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv — Alex Kudryashev, Apr 16 '19 at 19:04
I've tried to Edit my question and I hope that made it Understandable. — mehrab habibi, Apr 16 '19 at 19:14

score 1 · Accepted Answer · answered Apr 17 '19 at 08:27

        CREATE VIEW  [dbo].[ret_vwSalariedForReport]
AS
     WITH temp1 AS (SELECT
     salaried.*,
     operationalUnits.Title as OperationalUnitTitle
FROM
    ret_vwSalaried salaried LEFT JOIN
    prs_operationalUnitFeatures operationalUnitFeatures on salaried.[Guid] = operationalUnitFeatures.[FeatureGuid] LEFT JOIN 
    prs_operationalUnits operationalUnits ON operationalUnits.id = operationalUnitFeatures.OperationalUnitID 
    ), 
temp2 AS (SELECT
    t2.*,
    STUFF ((SELECT ' - ' + t1.OperationalUnitTitle
        FROM
            temp1 t1 
        WHERE t1.[ID] = t2.[ID]  
        For XML PATH('')), 2, 2, '') OperationalUnitTitles from temp1 t2) 
SELECT 
    [Guid],
    ID,
    Title,
    PersonnelNo,
    FirstName,
    LastName,
    FullName,
    Active,
    SSN,
    DeathDate,
    SalariedType,
    OperationalUnitTitles
FROM 
    temp2
GROUP BY 
    [Guid],
    ID,
    Title,
    PersonnelNo,
    FirstName,
    LastName,
    FullName,
    Active,
    SSN,
    DeathDate,
    SalariedType,
    OperationalUnitTitles

Here is the Correct Answer! – mehrab habibi Apr 17 '19 at 08:28 — mehrab habibi, Apr 17 '19 at 08:28
We Could Use STUFF and FOR XML – mehrab habibi Apr 17 '19 at 08:29 — mehrab habibi, Apr 17 '19 at 08:29

How to combine 2 or more rows in one and concatenate one column with comma Seperator

1 Answers1