SQL Basics-Multi-line query result stitching

Today in the work encountered the following this problem, you need to make the following query results, stitching into one line!

1 Select person_name from S_project_member where project_id = ' 2c9081473e2f8bda013e3028e4700049';

Let's go straight to the code! -------------------------have problems, please point out--------------------------------------------------------

Select      CAST(Stuff((        Select distinct ','+Person_name fromS_project_memberwhereproject_id= '2c9081473e2f8bda013e3028e4700049'  forXML Path ("')),1,1,"') as varchar( +)    )          asPerson_names fromS_project_memberwhereproject_id= '2c9081473e2f8bda013e3028e4700049' GROUP  byproject_id;

The knowledge points used here are:

FOR XML Path () function:

-- this is used for string concatenation.

CAST () function:

-- The cast function is used to explicitly convert an expression of a data type to another data type cast  as varchar ());

STUFF () function:

-- The STUFF () function deletes characters of a specified length and can insert another set of characters at the point at which it was established.  --Select Stuff (column name, start position, length, substitution string)

DISTINCT Keywords:

-- here can be understood as to go heavy, specific can Baidu

As keyword:

--

Grop by:

-- for grouping

Here's a concrete example: from: http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html

Create TableHobby (Hobbyidvarchar( +), Hnamevarchar( +))Insert  intoHobbyValues('1','Climbing');Insert  intoHobbyValues('2','Swimming');Insert  intoHobbyValues('3','Climbing');

-- output query results in XML format based on Rows SELECT *  from  for XML PATH;

-- change the name of the XML row node SELECT *  from  for XML PATH ('myhobby');

 --  change the name of the XML column node  select  hobbyid "  mycode  , Hname as   '  

-- Build the way we like to output SELECT '  '+hname+'  ]' from for XML PATH (');

Now I'm going to apply the example:

 --  apply   distinct   " ,   " +  hobbyid from  hobby for  XML Path ( "); 

-- The For XML Path (")  resolves the connection problem Select hname, (selectdistinct'+ ' +  from where =  FOR XML Path ("as from thegroupby Hname;

--stuff (string,1,1, ') removes the starting connector +1+2+3--and 1+2+3SelectHname,Stuff((Select distinct '+'+Hobbyid fromHobby forXML Path ("')),1,1,"') asA fromHobbyGroup  byHname;

--Query results required
' Mountain climbing ' 1 ' + ' 3 '
' Swim ' ' 2 '

--Method OneSelectHname,Stuff((Select distinct '+'+Hobbyid fromHobbywhereHname=B.hname forXML Path ("')),1,1,"') asA fromHobby BGroup  byHname;
--Method TwoSELECTB.hname, Left(Stulist,LEN(stulist)-1) asHobby from (SELECTHname, (SELECTHobbyid+','  fromHobbyWHEREHname=A.hname forXML PATH ("')) asstulist fromHobby AGROUP  byHname) B
Results:

The first time to write notes ... 2016.06.25

SQL Basics-Multi-line query result stitching