SQL合并列值两种新方法

      在SQL SERVER中，有时需要合多列值到行的需求，常见的方法有：1.创建自定义函数，2.使用游标法进行字符串合并。3.使用临

时表实现字符串合并。
     数据：

        DEPTNO EMPS
------ ----------
10 CLARK
10 KING
10 MILLER
20 SMITH
20 ADAMS
20 FORD
20 SCOTT
20 JONES
30 ALLEN
30 BLAKE
30 MARTIN
30 JAMES
30 TURNER
30 WARD

期望结果：

	 DEPTNO EMPS
------- ------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES



   方法：

- 1 .xmloutput -- -------------------------------------
-- http://wintersun.cnblogs.com
-- -------------------------------------------------
SELECT   *
FROM (
     SELECT   DISTINCT  
        deptno
     FROM  emp
)A
OUTER  APPLY(
     SELECT  
         [ values ] =   STUFF ( REPLACE ( REPLACE (
            (
                 SELECT  ENAME  FROM  emp N
                 WHERE  deptno  =  A.deptno
                 FOR  XML AUTO
            ),  ' <N ENAME=" ' ,  ' , ' ),  ' "/> ' ,  '' ),  1 ,  1 ,  '' )
)N

- 2 . -- -using CTE----------------------------------------
-- Creating a Delimited List from Table Rows
-- http://wintersun.cnblogs.com
-- -----------------------------------------------------
   with  x (deptno, cnt, list, empno,  len )
          as  (
      select  deptno,  count ( * )  over  (partition  by  deptno),
             cast (ename  as   varchar ( 100 )),
            empno,
             1
        from  emp
       union   all
      select  x.deptno, x.cnt,
             cast (x.list  +   ' , '   +  e.ename  as   varchar ( 100 )),
            e.empno, x. len + 1
        from  emp e, x
      where  e.deptno  =  x.deptno
        and  e.empno  >  x. empno
                )
      select  deptno,list
        from  x
       where   len   =  cnt
       order   by   1

测试的用的表与数据:

tableAndData
    
/*
 * SC Header, do not delete!
 *
 * $Revision:  $
 * $Date: 2008-11-30 $
 * $Author: Administrator $
 * $Archive: $
 *
 * Purpose:
 *   To recreate the Data in emp Table
 * Change History:
 *
 */

USE NORTHWND
GO
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Backup the table
--#region
DECLARE 
    @TimeStamp VARCHAR(30),
    @SQL       NVARCHAR(4000),
    @Revision  NVARCHAR(20),
    @StatusMessage VARCHAR(400)
    
SET @Revision = CAST(REPLACE(REPLACE('$Revision: $', 'Revision:', ''), '$', '') AS INT)    
SET @TimeStamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(25),GETDATE(),120),' ',''),'-',''),':','')   
SET @SQL = 'SELECT * INTO [dbo].[emp_' + @Revision + '_' + @TimeStamp + '] FROM [dbo].[emp]'
    
    EXEC sp_executesql @SQL

IF (@@ROWCOUNT = 0 AND (SELECT COUNT(*) FROM [dbo].[emp]) > 0)
OR (@@ERROR > 0)
BEGIN
    RAISERROR('not enough rows inserted into the backup table', 16, 1)
    GOTO ErrorHandler
END
ELSE
BEGIN
    SET @StatusMessage = 'All existing data was copied into the table [dbo].[emp_' + @Revision + '_' + @TimeStamp + ']'
    PRINT @StatusMessage
END
--#endregion

DELETE FROM [dbo].[emp]
IF @@ERROR > 0
BEGIN
    PRINT 'Delete Failed, roll back the transaction'
    GOTO ErrorHandler
END
    
-- Setup the XML document that is the table data, this text string can be up to 2gb in size.
--#region
DECLARE @DocumentHandle INT
EXEC dbo.sp_xml_preparedocument @DocumentHandle OUTPUT, N'
<?xml version="1.0" encoding="utf-16"?>
<empRows>
    <empRow>
        <EMPNO>7369</EMPNO>
        <ENAME>SMITH</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7902</MGR>
        <HIREDATE>17-DEC-1980</HIREDATE>
        <SAL>800</SAL>
        <COMM />
        <DEPTNO>20</DEPTNO>
    </empRow>
    <empRow>
        <EMPNO>7499</EMPNO>
        <ENAME>ALLEN</ENAME>
        <JOB>SALESMAN</JOB>
        <MGR>7698</MGR>
        <HIREDATE>20-FEB-1981</HIREDATE>
        <SAL>1600</SAL>
        <COMM>300</COMM>
        <DEPTNO>30</DEPTNO>
    </empRow>
    <empRow>
        <EMPNO>7521</EMPNO>
        <ENAME>WARD</ENAME>
        <JOB>SALESMAN</JOB>
        <MGR>7698</MGR>
        <HIREDATE>22-FEB-1981</HIREDATE>
        <SAL>1250</SAL>
        <COMM>500</COMM>
        <DEPTNO>30</DEPTNO>
    </empRow>
    <empRow>
        <EMPNO>7566</EMPNO>
        <ENAME>JONES</ENAME>
        <JOB>MANAGER</JOB>
        <MGR>7839</MGR>
        <HIREDATE>02-APR-1981</HIREDATE>
        <SAL>2975</SAL>
        <COMM />
        <DEPTNO>20</DEPTNO>
    </empRow>
    <empRow>
        <EMPNO>7654</EMPNO>
        <ENAME>MARTIN</ENAME>
        <JOB>SALESMAN</JOB>
        <MGR>7698</MGR>
        <HIREDATE>28-SEP-1981</HIREDATE>
        <SAL>1250</SAL>
        <COMM>1400</COMM>
        <DEPTNO>30</DEPTNO>
    </empRow>
    <empRow>
        <EMPNO>7698</EMPNO>
        <ENAME>BLAKE</ENAME>
        <JOB>MANAGER</JOB>
        <MGR>7839</MGR>
        <HIREDATE>01-MAY-1981</HIREDATE>
        <SAL>2850</SAL>
        <COMM />
        <DEPTNO>30</DEPTNO>
    </empRow>
    <empRow>
        <EMPNO>7782</EMPNO>
        <ENAME>CLARK</ENAME>
        <JOB>MANAGER</JOB>
        <MGR>7839</MGR>
        <HIREDATE>09-JUN-1981</HIREDATE>
        <SAL>2450</SAL>
        <COMM />
        <DEPTNO>10</DEPTNO>
    </empRow>
    <empRow>
        <EMPNO>7788</EMPNO>
        <ENAME>SCOTT</ENAME>
        <JOB>ANALYST</JOB>
        <MGR>7566</MGR>
        <HIREDATE>09-DEC-1982</HIREDATE>
        <SAL>3000</SAL>
        <COMM />
        <DEPTNO>20</DEPTNO>
    </empRow>
    <empRow>
        <EMPNO>7839</EMPNO>
        <ENAME>KING</ENAME>
        <JOB>PRESIDENT</JOB>
        <MGR />
        <HIREDATE>17-NOV-1981</HIREDATE>
        <SAL>5000</SAL>
        <COMM />
        <DEPTNO>10</DEPTNO>
    </empRow>
    <empRow>
        <EMPNO>7844</EMPNO>
        <ENAME>TURNER</ENAME>
        <JOB>SALESMAN</JOB>
        <MGR>7698</MGR>
        <HIREDATE>08-SEP-1981</HIREDATE>
        <SAL>1500</SAL>
        <COMM>0</COMM>
        <DEPTNO>30</DEPTNO>
    </empRow>
    <empRow>
        <EMPNO>7876</EMPNO>
        <ENAME>ADAMS</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7788</MGR>
        <HIREDATE>12-JAN-1983</HIREDATE>
        <SAL>1100</SAL>
        <COMM />
        <DEPTNO>20</DEPTNO>
    </empRow>
    <empRow>
        <EMPNO>7900</EMPNO>
        <ENAME>JAMES</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7698</MGR>
        <HIREDATE>03-DEC-1981</HIREDATE>
        <SAL>950</SAL>
        <COMM />
        <DEPTNO>30</DEPTNO>
    </empRow>
    <empRow>
        <EMPNO>7902</EMPNO>
        <ENAME>FORD</ENAME>
        <JOB>ANALYST</JOB>
        <MGR>7566</MGR>
        <HIREDATE>03-DEC-1981</HIREDATE>
        <SAL>3000</SAL>
        <COMM />
        <DEPTNO>20</DEPTNO>
    </empRow>
    <empRow>
        <EMPNO>7934</EMPNO>
        <ENAME>MILLER</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7782</MGR>
        <HIREDATE>23-JAN-1982</HIREDATE>
        <SAL>1300</SAL>
        <COMM />
        <DEPTNO>10</DEPTNO>
    </empRow>
</empRows>
'
--#endregion
INSERT INTO [dbo].[emp] (
    [EMPNO],
    [ENAME],
    [JOB],
    [MGR],
    [HIREDATE],
    [SAL],
    [COMM],
    [DEPTNO]
)
SELECT 
    [EMPNO],
    [ENAME],
    [JOB],
    [MGR],
    [HIREDATE],
    [SAL],
    [COMM],
    [DEPTNO]
FROM OPENXML(@DocumentHandle, 'empRows/empRow', 2) WITH ( 
    [EMPNO] INT,
    [ENAME] NVARCHAR(50),
    [JOB] NVARCHAR(50),
    [MGR] NVARCHAR(50),
    [HIREDATE] NVARCHAR(50),
    [SAL] NVARCHAR(50),
    [COMM] NVARCHAR(50),
    [DEPTNO] INT
)
-- important to clean up the memory consumed by the XML Document
EXEC dbo.sp_xml_removedocument @DocumentHandle


COMMIT TRANSACTION
ErrorHandler:
IF @@TRANCOUNT > 0
BEGIN
    ROLLBACK TRANSACTION
    PRINT 'Transaction for [dbo].[emp] Rolled Back'
END
GO

另说个题外话，之前想在SQL2005导入文本数据需要打SP2,后来直接用EMS.Data.Import.2007.for.SQL.Server完事，
软件体积才5M。