SQL2008 不用for xml 实现拼接字符串

因为for xml有些特殊符号 不能作为分割符号，所以采用这种方式。如果是SQL2012的话 有自带的函数了

        
--测试数据    
IF OBJECT_ID('tempdb..#tmp_Orgdata') IS NOT NULL
    DROP TABLE #tmp_Orgdata
GO
--GID 分组ID STRCOL 字符串
SELECT GID,StrCol
INTO #tmp_Orgdata
FROM (
SELECT '1' AS GID, 'A' AS StrCol
UNION ALL
SELECT '1' AS GID, 'B' AS StrCol
UNION ALL
SELECT '1' AS GID, 'C' AS StrCol
UNION ALL
SELECT '2' AS GID, 'A' AS StrCol
UNION ALL
SELECT '2' AS GID, 'B' AS StrCol
) a
GO
--原始数据 只有两列 这里多生成排序字段，这里可以根据需要按自己的排序来

IF OBJECT_ID('tempdb..#tmp_data') IS NOT NULL
    DROP TABLE #tmp_data
GO
SELECT GID,StrCol,ROW_NUMBER()OVER(PARTITION BY GID ORDER BY StrCol) AS Sort
INTO #tmp_data
FROM #tmp_Orgdata
GO

--递归拼，拼后只显示最后一行
;WITH cte AS (
SELECT GID,MAX(CAST(CASE WHEN Sort=1 THEN ','+StrCol ELSE '' END  AS VARCHAR(MAX))) AS StrCol,MIN(Sort) AS MinSort,MAX(Sort) AS MaxSort, 0 AS Flag
FROM #tmp_data 
GROUP BY GID
UNION ALL  
SELECT a.GID,a.StrCol+','+b.StrCol,b.Sort,a.MaxSort,CASE WHEN a.MaxSort=b.Sort THEN 1 ELSE 0 END AS Flag
FROM cte a,#tmp_data b 
WHERE a.GID=b.GID AND a.MinSort+1=b.Sort
)
SELECT * FROM cte where Flag=1
ORDER BY cte.GID