在SQL中分拆列值和合并列值老生常谈了，从网上搜刮了一下并记录下来，以便不时之需 :)

什么叫分拆列值和合并列值呢？就只是这样的，比如有如下表A结构及数据：

Id	Data
1	aa,bb
2	aaa,bbb,ccc

 

 

 

 

将该表A的Data字段数据根据 “,” 进行分拆得到如下表B

Id	Data
1	aa
1	bb
2	aaa
2	bbb
2	ccc

 

 

 

 

 

 

 

这就是表A-->表B 叫做分拆列值，表B-->表A 叫做合并列值。

一、分拆列值：

CREATE TABLE t_Demo1(  Id INT,  Data VARCHAR(30))GOINSERT INTO t_Demo1 VALUES(1,'aa,bb') INSERT INTO t_Demo1 VALUES(2,'aaa,bbb,ccc')

 分拆方法一：（古老方法，适合于SQL 2000）

--方法一：适用于SQL 2000SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO #t FROM syscolumns a, syscolumns bSELECT A.Id, SUBSTRING(A.Data, B.Id, CHARINDEX(',', A.Data + ',', B.Id) - B.Id) AS DataFROM t_Demo1 as A, #t as B WHERE SUBSTRING(',' + A.Data, B.Id, 1) = ',' DROP TABLE #t

 分拆方法二：（适合于SQL2005及以上版本）

--方法二：适用于SQL 2005及之后版本SELECT A.Id, B.Data FROM(     SELECT Id, Data = CONVERT(xml,' 
      
      
       ' + REPLACE(Data, ',', ' 
       
      
       ') + ' 
       
     ') FROM t_Demo1 )A OUTER APPLY(     SELECT Data = N.v.value('.', 'varchar(100)') FROM A.Data.nodes('/root/v') N(v) )B

 执行结果如下图：

二、合并列值：

REATE TABLE t_Demo2(  Id INT,  Data VARCHAR(30))GOINSERT INTO t_Demo2 VALUES(1, 'aa') INSERT INTO t_Demo2 VALUES(1, 'bb') INSERT INTO t_Demo2 VALUES(2, 'aaa') INSERT INTO t_Demo2 VALUES(2, 'bbb') INSERT INTO t_Demo2 VALUES(2, 'ccc')

合并方法一:（适用于SQL2000 版本，只能用函数的方式来实现）

--方法一（适用于SQL2000 版本只能用函数的方式来实现）:CREATE FUNCTION dbo.Fun_GetStr(@id INT) RETURNS VARCHAR(8000) AS BEGIN  DECLARE @r VARCHAR(8000)  SET @r = ''  SELECT @r = @r + ',' + CAST(Data AS VARCHAR) FROM t_Demo2 WHERE id=@id  SET @r=STUFF(@r, 1, 1, '') --或者 --SET @r=RIGHT(@r , len(@r) - 1)  RETURN @rEND GO--使用该函数SELECT Id, Data = dbo.Fun_GetStr(Id) FROM t_Demo2 GROUP BY Id--或者SELECT DISTINCT Id ,dbo.Fun_GetStr(Id) AS Data FROM t_Demo2 --删除该函数DROP FUNCTION dbo.Fun_GetStrGO

 合并方法二:（适用于SQL2005及其以后版本）

--方法二(适用于SQL2005及其以后版本)SELECT Id, Data=STUFF((SELECT ','+ Data FROM t_Demo2 AS t WHERE Id=t_Demo2.Id FOR XML PATH('')), 1, 1, '') FROM t_Demo2 GROUP BY Id

 执行结果如下图:

三、合并列值并使用聚合函数：

CREATE TABLE t_Price(  Id INT,  Salary FLOAT,   Name NVARCHAR(10))GOINSERT INTO t_Price VALUES(1,10,'小明')INSERT INTO t_Price VALUES(1,20,'小红')INSERT INTO t_Price VALUES(2,50,'小兰')INSERT INTO t_Price VALUES(2,100,'小七')GO--方法一:(利用函数)CREATE FUNCTION Fun_HbStr(@id int) RETURNS NVARCHAR(100)ASBEGIN    DECLARE @s NVARCHAR(100)    SELECT @s=ISNULL(@s+',','')+ CAST(Name AS NVARCHAR) FROM t_Price where id=@id    RETURN @sENDGOSELECT Id ,SUM(Salary) AS Salary,dbo.Fun_HbStr(Id) AS Data FROM t_Price GROUP BY Id--删除函数DROP FUNCTION Fun_HbStrGO--方法二：SELECT Id, SUM(Salary) AS Salary,name=STUFF((SELECT ','+ Name FROM t_Price AS t WHERE Id=t_Price.Id FOR XML PATH('')), 1, 1, '') FROM t_Price GROUP BY Id --删除表 t_PriceDROP TABLE t_PriceGO

 执行结果如下图：