SQL合并列值两种新方法

最新推荐文章于 2023-06-29 15:19:15 发布

weixin_33726318

最新推荐文章于 2023-06-29 15:19:15 发布

阅读量202

点赞数

在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。