In SQL, it is often necessary to synthesize multiple rows of data into one line below is a brief introduction to using the for XML path.
1, convert the Tuyi to figure two:
1 select ArticleID, 2 (select convert (varchar (10 ), TagID) + " " from 3 for XML PATH ( Span style= "color: #ff0000;" ' ') as tagID from ARTICLETAG_TB group by ArticleID
The above is a table, the following look at the practice of multi-table association, is actually the same as just the connection table query
This is an example of an article that corresponds to more than one label three sheets:
CREATE TABLE [dbo].[ARTICLE_TB]( [ArticleID] [int] NULL, [ArticleName] [nvarchar]( -)NULL)CREATE TABLE [dbo].[ARTICLETAG_TB]( [ArticleID] [int] NULL, [TagID] [int] NULL)CREATE TABLE [dbo].[TAG_TB]( [TagID] [int] NULL, [TagName] [nvarchar]( -)NULL)
Implement the SQL statement:
SELECTArticleName, (SELECTTagName+',' from(SELECTA.articlename,t.tagname fromARTICLE_TB A Left JOINARTICLETAG_TB at onAt.articleid=A.articleid Left JOINTAG_TB T onAt.tagid=T.tagid)TempWHERE Temp. articlename=Tt.articlename forXML PATH ("')) asTagName fromARTICLE_TB TT
Explain:
Connect three tables first to find the required fields, and then use the for XML PATH self-Join stitching string
The result of a three-table connection query is: Using the for XML path stitching result:
SQL uses the for XML path to implement string concatenation