--各种字符串分函数 SQL

-- 3.3.1 使用游标法进行字符串合并处理的示例。
-- 处理的数据
CREATE TABLE tb(col1 varchar ( 10 ),col2 int )
INSERT tb SELECT ' a ' , 1
UNION ALL SELECT ' a ' , 2
UNION ALL SELECT ' b ' , 1
UNION ALL SELECT ' b ' , 2
UNION ALL SELECT ' b ' , 3

-- 合并处理
-- 定义结果集表变量
DECLARE @t TABLE (col1 varchar ( 10 ),col2 varchar ( 100 ))

-- 定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY   col1,col2
DECLARE @col1_old varchar ( 10 ), @col1 varchar ( 10 ), @col2 int , @s varchar ( 100 )
OPEN tb
FETCH tb INTO @col1 , @col2
SELECT @col1_old = @col1 , @s = ''
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @col1 = @col1_old
        SELECT @s = @s + ' , ' + CAST ( @col2 as varchar )
    ELSE
    BEGIN
        INSERT @t VALUES ( @col1_old , STUFF ( @s , 1 , 1 , '' ))
        SELECT @s = ' , ' + CAST ( @col2 as varchar ), @col1_old = @col1
    END
    FETCH tb INTO @col1 , @col2
END
INSERT @t VALUES ( @col1_old , STUFF ( @s , 1 , 1 , '' ))
CLOSE tb
DEALLOCATE tb
-- 显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
/* --结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
-- */
GO


/* ============================================== */


-- 3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例
-- 处理的数据
CREATE TABLE tb(col1 varchar ( 10 ),col2 int )
INSERT tb SELECT ' a ' , 1
UNION ALL SELECT ' a ' , 2
UNION ALL SELECT ' b ' , 1
UNION ALL SELECT ' b ' , 2
UNION ALL SELECT ' b ' , 3
GO

-- 合并处理函数
CREATE FUNCTION dbo.f_str( @col1 varchar ( 10 ))
RETURNS varchar ( 100 )
AS
BEGIN
    DECLARE @re varchar ( 100 )
    SET @re = ''
    SELECT @re = @re + ' , ' + CAST (col2 as varchar )
    FROM tb
    WHERE col1 = @col1
    RETURN ( STUFF ( @re , 1 , 1 , '' ))
END
GO

-- 调用函数
SELECT col1,col2 = dbo.f_str(col1) FROM tb GROUP BY col1
-- 删除测试
DROP TABLE tb
DROP FUNCTION f_str
/* --结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
-- */
GO

/* ============================================== */


-- 3.3.3 使用临时表实现字符串合并处理的示例
-- 处理的数据
CREATE TABLE tb(col1 varchar ( 10 ),col2 int )
INSERT tb SELECT ' a ' , 1
UNION ALL SELECT ' a ' , 2
UNION ALL SELECT ' b ' , 1
UNION ALL SELECT ' b ' , 2
UNION ALL SELECT ' b ' , 3

-- 合并处理
SELECT col1,col2 = CAST (col2 as varchar ( 100 ))
INTO #t FROM tb
ORDER BY col1,col2
DECLARE @col1 varchar ( 10 ), @col2 varchar ( 100 )
UPDATE #t SET
    @col2 = CASE WHEN @col1 = col1 THEN @col2 + ' , ' + col2 ELSE col2 END ,
    @col1 = col1,
    col2 = @col2
SELECT * FROM #t
/* --更新处理后的临时表
col1       col2
---------- -------------
a          1
a          1,2
b          1
b          1,2
b          1,2,3
-- */
-- 得到最终结果
SELECT col1,col2 = MAX (col2) FROM #t GROUP BY col1
/* --结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
-- */
-- 删除测试
DROP TABLE tb,#t
GO


/* ============================================== */

-- 3.3.4.1 每组 <=2 条记录的合并
-- 处理的数据
CREATE TABLE tb(col1 varchar ( 10 ),col2 int )
INSERT tb SELECT ' a ' , 1
UNION ALL SELECT ' a ' , 2
UNION ALL SELECT ' b ' , 1
UNION ALL SELECT ' b ' , 2
UNION ALL SELECT ' c ' , 3

-- 合并处理
SELECT col1,
    col2 = CAST ( MIN (col2) as varchar )
        + CASE
            WHEN COUNT ( * ) = 1 THEN ''
            ELSE ' , ' + CAST ( MAX (col2) as varchar )
        END
FROM tb
GROUP BY col1
DROP TABLE tb
/* --结果
col1       col2     
---------- ----------
a          1,2
b          1,2
c          3
-- */

-- 3.3.4.2 每组 <=3 条记录的合并
-- 处理的数据
CREATE TABLE tb(col1 varchar ( 10 ),col2 int )
INSERT tb SELECT ' a ' , 1
UNION ALL SELECT ' a ' , 2
UNION ALL SELECT ' b ' , 1
UNION ALL SELECT ' b ' , 2
UNION ALL SELECT ' b ' , 3
UNION ALL SELECT ' c ' , 3

-- 合并处理
SELECT col1,
    col2 = CAST ( MIN (col2) as varchar )
        + CASE
            WHEN COUNT ( * ) = 3 THEN ' , '
                + CAST (( SELECT col2 FROM tb WHERE col1 = a.col1 AND col2 NOT IN ( MAX (a.col2), MIN (a.col2))) as varchar )
            ELSE ''
        END
        + CASE
            WHEN COUNT ( * ) >= 2 THEN ' , ' + CAST ( MAX (col2) as varchar )
            ELSE ''
        END
FROM tb a
GROUP BY col1
DROP TABLE tb
/* --结果
col1       col2
---------- ------------
a          1,2
b          1,2,3
c          3
-- */
GO

DECLARE @t TABLE (id int , value varchar ( 10 ))
INSERT @t SELECT 1 , ' aa '
UNION ALL SELECT 1 , ' bb '
UNION ALL SELECT 2 , ' aaa '
UNION ALL SELECT 2 , ' bbb '
UNION ALL SELECT 2 , ' ccc '


-- 查询处理
SELECT * FROM ( SELECT DISTINCT id FROM @t )A
OUTER APPLY( SELECT
[ values ] = STUFF ( REPLACE ( REPLACE ((
SELECT value FROM @t N WHERE id = A.id
FOR XML AUTO), ' <N value=" ' , ' , ' ), ' "/> ' , '' ), 1 , 1 , '' ))N

-- 结果
-- --------- ----------------
1            aa,bb
2            aaa,bbb,ccc