CODEWITHSUNDEEP
sql-server
mehrab habibi
mehrab habibi

Reputation: 427

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

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.

Upvotes: 0

Views: 261

Answers (1)

mehrab habibi
mehrab habibi

Reputation: 427

        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

Upvotes: 1

Related Questions