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