有表A,
id Name
1 1
1 2
1 3
2 1
2 2
3 1
转化成表B:
id Name
1 1,2,3
2 1,2
3 1
------------------------------------------------------------------------
现接受两个方法,首先创建表
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
CREATE TABLE TEST
(
ID VARCHAR(36),
Name VARCHAR(36)
)
INSERT INTO TEST1 VALUES ('1',1)
INSERT INTO TEST1 VALUES ('1',2)
INSERT INTO TEST1 VALUES ('1',3)
INSERT INTO TEST1 VALUES ('2',1)
INSERT INTO TEST1 VALUES ('2',2)
INSERT INTO TEST1 VALUES ('3',1
------------------------------------------------------------------------
1 利用sql查询对结果集进行连接
声明一个函数,返回连接之后的字符串
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
CREATE FUNCTION GetNameByID(@ID VARCHAR(36))
RETURNS VARCHAR(2000)
AS
BEGIN
declare @str varchar(200)
set @str=''
select @str=@str+','+cast(Name as varchar) from TEST where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
最后执行 SELECT DISTINCT ID,dbo. GetNameByID(ID) FROM TEST
2 利用游标来返回字符串
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
CREATE FUNCTION GetName(@ID VARCHAR(36))
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @Str VARCHAR(200)
DECLARE @Name VARCHAR(200)
DECLARE Name_cursor CURSOR FOR
SELECT Name FROM Test1 Where ID=@ID
OPEN Name_cursor
FETCH NEXT FROM Name_cursor INTO @Str
SET @Name=''
WHILE(@@FETCH_STATUS=0)
BEGIN
SELECT @Name=@Name+','+@Str
FETCH NEXT FROM Name_cursor INTO @Str
END
SELECT @Name=right(@Name,LEN(@Name)-1)
CLOSE Name_cursor
DEALLOCATE Name_cursor
RETURN @Name
最后执行 SELECT DISTINCT ID,dbo. GetNameByID(ID) FROM TEST
Code
Code
Code