Drop Table A ---- Test Environment -------- Connect the same group of fields Create Table A (ID1 Int, ID2 VARCHAR (50)) Insert Into A SELECT 2, 1 Union All SELECT 2 , 2 Union All Select 2, 3 Union All Select 2, 5 Union All Select 3, 2 Union All Select 3, 3 Union Allselect 3, 5 Union AllSelect 3, 5iF EXISTS (SELECT 1 from sysobjects where name = 'Combine' and xtype = 'fn') DROP FUNCTION Combine ------ Write function Implement Integration Function Create Function Combine (@vcha varchar (10)) Returns varchar (8000) asbegindeclare @r varchar (8000) SET @ R = '' SELECT @ r = @ r ',' ID2 from a where id1 = @ vcha return (Substring (@ r, 2,8000)) endGO ------- Execute Statement Select ID1, ID2 = DBO .combine (id1) from a group by id1
ID1 ID2
2 1, 2, 3, 3, 53 2, 3, 5, 5 ---- Delete Test Environment --- Drop Function Combine --- Drop Table A -------------- Common problem restore problem --------------------------------- Test Environment - Request from - Table A (ID1, ID2) DROP TABLE A, B, # Acreate Table A (ID1 Int, ID2 VARCHAR (100)) Insert Ite Select 2, '1, 2, 3' Union All Select 2, '3, 5' Union All SELECT 3, '2, 3, 5' Union All SELECT 3, '5'
- Conversion to table B id1 ID2 2 1 2 2 2 3 2 3 2 5 3 2 3 3 3 5 3 5
---- Establishing must be conditioned Select * INTO B from a where 1 <> 1 --- Copy Table Structure to Bselect * Into #a from A --- import data into virtual table
---- Cycling results declare @a int set @ a = 1WHILE (@a <> 0) Begin
INSERT INTO B SELECT * FROM #A Where Patindex ('%,%', ID2) = 0
Delete from #a where patindex ('%,%', ID2) = 0
INSERT INTO B SELECT ID1, LEFT (ID2, Patindex ('%,%', ID2) -1) from #aupdate #a set id2 = stuff (id2, 1, patindex ('%,%, id2),' ' ) SELECT @ a = count (*) from #aend
