How to concatenate multiple rows to single row without messing up the data?-CodePudding

I am new to sql server and found a problem in using the FOR XML code. My report builder is using sql server 2014.

I have the table like:

Field	Accessory
1.I	AA
1.I	BB
1.I	CC
1.II	AA
1.III	AA
1.III	BB
1.IV	AA
1.IV	BB
1.V	AA

And the result requires to be:

Field	Accessory
1.I	AA,BB,CC
1.II	AA
1.III	AA,BB
1.IV	AA,BB
1.V	AA

But instead, I’ve got this:

Field	Accessory
1.I	AA,BB,CC,AA,AA,BB,AA,BB,AA
1.II	AA,BB,CC,AA,AA,BB,AA,BB,AA
1.III	AA,BB,CC,AA,AA,BB,AA,BB,AA
1.IV	AA,BB,CC,AA,AA,BB,AA,BB,AA
1.V	AA,BB,CC,AA,AA,BB,AA,BB,AA

Is it appropriate to use FOR XML path for this kind of table grouping? Thanks!

The query is :

SELECT
       Radi.Field,
       (SELECT
               Acce.AccessoryId ','
        FROM
               FieldAcce
               INNER JOIN Radi ON FieldAcce.RadiSer = Radi.RadiSer
               INNER JOIN Acce ON FieldAcce.AcceSer = Acce.AcceSer
        WHERE
               Radi.Id LIKE UPPER (@RNO)
               AND Radi.CourseID LIKE @CourseID
               AND Radi.PhaseId LIKE @PhaseID
        ORDER BY
               Radi.Field
        FOR XML PATH('')) AS [Accessory]
FROM
        FieldAcce
        INNER JOIN Radi ON FieldAcce.RadiSer = Radi.RadiSer
        INNER JOIN Acce ON FieldAcce.AcceSer = Acce.AcceSer
WHERE
        Radi.Id LIKE UPPER (@RNO)
        AND Radi.CourseID LIKE @CourseID
        AND Radi.PhaseId LIKE @PhaseID
GROUP BY
        Radi.Field
        Acce.AccessoryId
ORDER BY
        Radi.Field

The new query is as followed (It works when the STUFF() part have not added):

SELECT
    r.Field,
    STUFF(  
        (SELECT
            ', '   Acce.AccessoryId
         FROM
            Radi
            INNER JOIN FieldAcce ON Radi.RadiSer = FieldAcce.RadiSer
            LEFT OUTER JOIN Acce ON FieldAcce.AcceSer = Acce.AcceSer
         WHERE
            Radi.Id LIKE UPPER (@RNO)
            AND Radi.CourseId LIKE @CourseID
            AND LEFT (Radi.PlanSetupId,1) LIKE @PhaseID
            AND r.Field = Radi.Field
         ORDER BY
            Radi.Field
         FOR XML PATH(''))
          ), 1, 1, '') AS [Accessory]
FROM
    Radi r
    INNER JOIN FieldAcce ON r.RadiSer = FieldAcce.RadiSer
    LEFT OUTER JOIN Acce ON FieldAcce.AcceSer = Acce.AcceSer
WHERE
    Radi.Id LIKE UPPER (@RNO)
    AND Radi.CourseId LIKE @CourseID
    AND LEFT (Radi.PlanSetupId,1) LIKE @PhaseID
    AND EXISTS
        (SELECT
            r.Field
         FROM
            Radi cry
         WHERE
            r.Field = cry.Field
            AND cry.Field NOT LIKE N'APER%')
GROUP BY
    r.Field
ORDER BY
    r.Field

Sample data for Accessory is

Accessory
EDW45IN
A10
A06
EDW60IN
EDW45OUT
NDS
A084

CodePudding user response：

The sub-query should reference the main query Field and AccessoryId

SELECT
       Radi.Field,
       (SELECT
               a.AccessoryId ','
        FROM
               FieldAcce fa
               INNER JOIN Radi r ON fa.RadiSer = r.RadiSer
               INNER JOIN Acce a ON fa.AcceSer = a.AcceSer
        WHERE
               r.Id LIKE UPPER (@RNO)
               AND r.CourseID LIKE @CourseID
               AND r.PhaseId LIKE @PhaseID
               -- add the following 2 lines
               AND r.Field = Radi.Field
               AND a.AccessoryId = Acce.AccessoryId
        ORDER BY
               r.Field
        FOR XML PATH('')) AS [Accessory]
FROM
        FieldAcce
        INNER JOIN Radi ON FieldAcce.RadiSer = Radi.RadiSer
        INNER JOIN Acce ON FieldAcce.AcceSer = Acce.AcceSer
WHERE
        Radi.Id LIKE UPPER (@RNO)
        AND Radi.CourseID LIKE @CourseID
        AND Radi.PhaseId LIKE @PhaseID
GROUP BY
        Radi.Field,
        Acce.AccessoryId
ORDER BY
        Radi.Field