0

I know this is somehow redundant question but please understand.

first - I've research all the resources I could here in stackoverflow and outside of it and I found some solution close to my desired ouput.

second - I made my own query from what I've researched, so far It is running.

third - my desired output from my query is not what I've expected.

My current table(in view) design is this which is unconcatenated.

+----------------------+
| groupID | vesselName |
+----------------------+
|    2    | ACRO       |
+----------------------+
|    1    | JANICE 1   |
+----------------------+
|    1    | P/S-B      |
+----------------------+

this is my query

SELECT
    HRDB.dbo.vRangerGroup.groupID,
    STUFF(
        (
            SELECT HRDB.dbo.vRangerGroup.vesselName + ',' AS vesselName
            FROM HRDB.dbo.vRangerGroup AS RGS
            WHERE HRDB.dbo.vRangerGroup.groupID = RGS.groupID
            FOR XML PATH('')
        ),1,0,''
    ) AS RESULT
FROM HRDB.dbo.vRangerGroup

and here the output of my query

+------------------------------------------------------------------------------+
| groupID | vesselName                                                         |
+------------------------------------------------------------------------------+
|    1    | <vesselName>JANICE1,</vesselName><vesselName>JANICE1,</vesselName> |
+------------------------------------------------------------------------------+
|    2    | <vesselName>ACRO,</vesselName>                                     |
+------------------------------------------------------------------------------+
|    1    | <vesselName>P/S-B,</vesselName><vesselName>P/S-B,</vesselName>     |
+------------------------------------------------------------------------------+

Desired Output

+---------------------------+
| groupID | vesselName      |
+---------------------------+
|    2    | ACRO            |
+---------------------------+
|    1    | JANICE1, P/S-B  |
+---------------------------+

I don't know what's wrong with my query.

Devs
  • 65
  • 8
  • `SELECT v.groupID, STUFF(( SELECT ',' + RGS.vesselName AS vesselName FROM vRangerGroup AS RGS WHERE RGS.groupID = v.groupID FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 1, '') AS RESULT FROM vRangerGroup v GROUP BY v.groupID` – Felix Pamittan Feb 02 '16 at 02:29
  • WOW! it works like magic, what is the explanation between my FOR XML PATH(''),1,0,'' to your FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 1, '') or do you have some links for me to study? – Devs Feb 02 '16 at 02:37
  • Here is an [article](http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation) by Aaron Bertrand. Search for `More on FOR XML PATH` and you;ll see the explanation. – Felix Pamittan Feb 02 '16 at 02:41
  • Here is another [article](http://sqlblog.com/blogs/rob_farley/archive/2010/04/15/handling-special-characters-with-for-xml-path.aspx). – Felix Pamittan Feb 02 '16 at 02:42

0 Answers0