数据合并拆分（Data merge resolution）.docVIP

数据合并拆分（Data merge resolution） Table tb, as follows: ID value At 1 AA 1 BB 2 AAA 2 BBB 2 CCC Need to get results: ID values Round and round 1 AA, BB 2 AAA, BBB, CCC That is, group by ID, value (string and add) The solution of 1. old 1. create function CREATE FUNCTION dbo.f_str (@id int) RETURNS varchar (8000) AS BEGIN DECLARE @r varchar (8000) SET @r = SELECT @r = @r + value + . FROM TB WHERE id=@id RETURN STUFF (@r, 1, 1 ) END GO - call function SELECt ID values=dbo.f_str (ID) FROM TB GROUP BY ID 2. new solutions - sample data 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 Query processing * 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) The results of /*-- ID values --------------------------- 1 AA, BB 2 AAA, BBB, CCC (2 rows affected) --*/ - various string function --3.3.1 use vernier method string merging processing example. Processing data CREATE TABLE TB (col1 varchar (10), col2 int) INSERT TB SELECTa, 1 UNION ALL SELECTa, 2 UNION ALL SELECTb, 1 UNION ALL SELECTb, 2 UNION ALL SELECTb, 3 Merge processing The results in table variable definitions DECLARE @t TABLE (col1 varchar (10), col2 varchar (100)) - definition of cursor and merge 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 Delete the test data and the results show that: SELECT * FROM @t DROP TAB