游标例子

SQLSERVER 游标的使用

解决问题
解决表关联后从表数据字段的串联操作

测试数据

--建表
create table Family
(
    FID int primary key,
    ParentName varchar(20),
    Addr       varchar(50)
)
  
create table Children
(
    CID int identity(1,1) primary key, 
    FID int,
    ChildName varchar(20),
    Age int, 
)
  
--测试数据
insert into Family (FID, ParentName,Addr) values (1, '张三', '中华大街');
insert into Family (FID, ParentName,Addr) values (2, '李四', '云海南路');
insert into Family (FID, ParentName,Addr) values (3, '王五', '和平小巷');
 
 
insert into Children (FID, ChildName,Age) values (1, '张小', 10); 
insert into Children (FID, ChildName,Age) values (2, '李一', 8);
insert into Children (FID, ChildName,Age) values (2, '李二', 12);
insert into Children (FID, ChildName,Age) values (3, '王龙', 13);
insert into Children (FID, ChildName,Age) values (3, '王虎', 10);
insert into Children (FID, ChildName,Age) values (3, '王凤', 7);

希望得到这样的查询结果
FID ParentName Addr ChildName Age ChildName Age ChildName Age
1 张三中华大街张小 10
2 李四云海南路李一 8 李二 12
3 王五和平小巷王龙 13 王虎 10 王凤 7

数据库脚本

SELECT * FROM Children               
DROP TABLE #TEMP
GO
CREATE TABLE #TEMP
(
FID INT NOT NULL,
ParentName VARCHAR(20) NOT NULL,
Addr VARCHAR(50),
ChildNames VARCHAR(8000)
)
 
DECLARE @FID INT,@ParentName VARCHAR(20),@Addr VARCHAR(50), @ChildName VARCHAR(20),@Age INT
DECLARE @COUNT BIT
DECLARE MY_CURSOR CURSOR FOR SELECT family.FID,family.ParentName,family.Addr,Children.ChildName,Children.Age FROM family LEFT JOIN Children ON family.FID = Children.FID
OPEN MY_CURSOR 
FETCH NEXT FROM MY_CURSOR INTO @FID ,@ParentName ,@Addr , @ChildName ,@Age
WHILE @@FETCH_STATUS =0
BEGIN
    SELECT @COUNT= COUNT(FID) FROM #TEMP WHERE FID = @FID
    IF @COUNT > 0
        BEGIN
            UPDATE #TEMP SET ChildNames = ChildNames +' '+@ChildName +' '+CONVERT(VARCHAR,@Age) WHERE  FID = @FID
        END
    ELSE
        INSERT INTO  #TEMP(FID,ParentName,Addr,ChildNames) VALUES (@FID,@ParentName,@Addr,@ChildName + ' '+CONVERT(VARCHAR,@Age))
    FETCH  NEXT  FROM MY_CURSOR INTO @FID ,@ParentName ,@Addr , @ChildName ,@Age
    PRINT      @FID PRINT @ParentName PRINT @Addr PRINT @ChildName PRINT @Age
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR 
 
SELECT * FROM #TEMP