时表实现字符串合并。
数据:
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
-- 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
测试的用的表与数据:
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/*
* 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。