For XML PATH application and its inverse decomposition

Database environment: SQL SERVER 2005

We do this by merging the data contents of the same group into a single row, through the for XML path.

With data 1, to achieve the effect of Figure 2

1. The For XML path implementation of Figure 1 to Figure 2

There are many ways to introduce for XML on the Internet, which is no longer in detail, and interested friends can go and inquire about its usage.

--Data Preparation; withx0 as(SELECT   1  asID,'001'  asTyUNION  All               SELECT   1  asID,'002'  asTyUNION  All               SELECT   2  asID,'003'  asTyUNION  All               SELECT   3  asID,'004'  asTyUNION  All               SELECT   3  asID,'1234'  asTyUNION  All               SELECT   4  asID,' on'  asTyUNION  All               SELECT   4  asID,'005'  asTyUNION  All               SELECT   4  asID,'006'  asty)/*Implement*/    SELECTID,STUFF(Ty,1,1,"') asTy from(SELECTID, (SELECT    ',' +X2.ty fromx0 x2WHEREX2.id=x1.id forXML PATH ("')                        )  asTy fromx0 x1GROUP  byID) T

View Code

2. The recursive implementation of Figure 2 to Figure 1

From Figure 2 to Figure 1, the implementation of the method is not only recursive one method, you can try to solve with other methods.

/*Preparing Data*/ withx0 as(SELECT   1  asID,'001,002'  asTyUNION  All               SELECT   2  asID,'003'  asTyUNION  All               SELECT   3  asID,'004,1234'  asTyUNION  All               SELECT   4  asID,'01,005,006'  asty), X1 (ID, ty1, ty2) as(SELECTID, Case  when CHARINDEX(',', Ty,1)> 0                              Then CONVERT(VARCHAR(Ten), Left(Ty,CHARINDEX(',', Ty,1)- 1))                             ELSETyEND  asTy1,--this split character                         Case  when CHARINDEX(',', Ty,1)> 0  Then                              STUFF(Ty+ ',',1,CHARINDEX(',', Ty),"')                             ELSE NULL                        END  asTy2--Pending Split String                fromx0UNION  All               SELECTID,CONVERT(VARCHAR(Ten), Left(Ty2,Nullif(CHARINDEX(',', Ty2,1),                                                         0)- 1)) asTy1,--this split character                        STUFF(Ty2,1,CHARINDEX(',', Ty2),"') asTy2--Pending Split String                fromX1WHERE    CHARINDEX(',', Ty2,1)> 0             )    SELECTId,ty1 asTy fromX1ORDER  byId

View Code

For XML PATH application and its inverse decomposition