An example in the Marco's
Table T1,
ID PID
1 1
1 2
1 3
twenty one
twenty two
3 1
How to become T2:
ID PID
1 1, 2, 3
2 1, 2
3 1
A typical way is to use a custom function
--1. Create a merged function
Create Function Ferg (@ID int)
Returns varchar (8000)
AS
Begin
Declare @str varchar (8000)
Set @ Str = ''
Select @ str = @ Str ',' Cast (PID as varchar) from T1 where id = @ id
Set @ Str = Right (@ Str, Len (@str) -1)
Return (@STR)
End
Go
- Call the custom function to get the result Select DistINCT ID, DBO.FMERG (ID) from T1 but cannot use custom functions in SQL Server 7.0, what should I do? Ten Declare Cursor_t1 Cursor for Select Distinct ID from T1Declare @ID INTDECLARE @PID VARCHAR (100) IF Object_ID ('T2') IS Not Null - Constructs T2 Table Structure Drop Table T2Create Table T2 (ID INT, PID VARCHAR (100 )) OPEN cursor_t1FETCH NEXT fROM cursor_t1 INTO @idWHILE @@ FETCH_STATUS = 0BEGINset @pid = '' declare @len intselect @ len = count (1) from t1 where id = @ idif object_id ( 'tempdb .. # tmp') is not NULL - Texture Temporal Drop Table #tmpselect Identity (Int, 1, 1) AS NewID, ID, PID INTO #TMP from T1 Where ID = @ iDWHILE @LEN> 0 - By cyclic splicing string beginselect @PID = @ PID ',' CAST (PID As Varchar) from #tmp where [newid] = @ lenset @ len = @ len-1ENDDROP TABLE #Tmpset @ pid = reverse (Right (@ pid, len (@PID) -1) ) INSERT INTO T2 SELECT @ID, @PID - Insert result to T2 table fetch next from cursor_t1 @IDENDCLOSE CURSOR_T1DEALLOCATE CURSOR_T1SELECT *WOM T2 / * SELECT * FROM T2 * / ID PID ------------ ---------- 1 1, 2, 32 1, 23 1
(The number of rows affects is 3 lines)
