T-SQL rows merged into columns and columns split rows

The source of this article: http://www.cnblogs.com/wy123/p/6910468.html

I feel the recent SQL has not written less, suddenly a little rusty, for use is not too frequent some operations, time is easy to live.

One column of multiple rows merged into one column

CREATE TABLETestcolumnmergeandsplit (Idint Identity(1,1), Businessidint, Businessvaluevarchar( -))GOINSERT  intoTestcolumnmergeandsplitSELECT 1,'AAA'UNION  AllSELECT 1,'BBB'UNION  AllSELECT 1,'CCC'UNION  AllSELECT 2,'XXX'UNION  AllSELECT 2,'YYY'UNION  AllSELECT 2,'ZZZ'GOSELECT *  fromTestcolumnmergeandsplitGOSELECTt1. Businessid,STUFF       (            ( SELECT     ','+T2. Businessvalue fromtestcolumnmergeandsplit T2WHERET2. Businessid=t1. Businessid forXML PATH ("')            ),            1,1,"'        )          ascolnames fromTestcolumnmergeandsplit T1GROUP  byt1. BusinessidGO

In addition, a reverse operation, with the result of merging above, splits a string column of multiple values into multiple rows,

　　

A string splitter function used to compare common

CREATE FUNCTION [dbo].[fn_splitstringtotable] (    @s     VARCHAR(Max),    @split VARCHAR(Ten))RETURNS @re TABLE(IdINT IDENTITY(1,1), ValueVARCHAR( -)        ) asBEGIN    IF @s  is NULL        RETURN    IF @split  is NULL        RETURN    IF(LEN(@split)<=0)    BEGIN        INSERT  into @re VALUES(@s)        RETURN    END    DECLARE @splitlen INT    SET @splitlen=LEN(@split)- 1     while CHARINDEX(@split,@s)> 0    BEGIN        INSERT @re VALUES( Left(@s,CHARINDEX(@split,@s)- 1))        SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+ @splitlen,"')    END    INSERT @re VALUES(@s)    RETURNEND

Test scripts, no technical content

CREATE TABLETestcolumnmergeandsplit (Idint Identity(1,1), Businessidint, Businessvaluevarchar( -))GOINSERT  intoTestcolumnmergeandsplitSELECT 1,'AAA'UNION  AllSELECT 1,'BBB'UNION  AllSELECT 1,'CCC'UNION  AllSELECT 2,'XXX'UNION  AllSELECT 2,'YYY'UNION  AllSELECT 2,'ZZZ'GOSELECT *  fromTestcolumnmergeandsplitGO--merge multiple column columnsSELECTt1. Businessid,STUFF       (            ( SELECT     ','+T2. Businessvalue fromtestcolumnmergeandsplit T2WHERET2. Businessid=t1. Businessid forXML PATH ("')            ),            1,1,"'        )          ascolstring fromTestcolumnmergeandsplit T1GROUP  byt1. BusinessidGOSELECT *  fromTestcolumnmergeandsplit_bakGOSELECTt1. Businessid,t1. Colstring,t2. Id,t2. Value fromTestcolumnmergeandsplit_bak T1 CrossAPPLY dbo.fn_splitstringtotable (colstring,',') T2GO

　　

T-SQL rows merged into columns and columns split rows