2行数据只有一列数据不同（不是主键），怎么合并成1行？

来源：互联网发布：mac 隐藏文件夹显示编辑：程序博客网时间：2024/04/30 15:26

create table tb(id int, value nvarchar(50)) insert into tb values(1, 'aa') ;insert into tb values(1, 'bb');insert into tb values(2, 'aaa') ;insert into tb values(2, 'bbb');insert into tb values(2, 'ccc') ;goselect * from tb --1. 创建处理函数create function   dbo.f_value(@id int) returns  varchar(8000) asbegin declare  @str varchar(8000) set @str = ''select  @str = @str + '/' + value  from  tb where id=@id return stuff(@str, 1, 1, '') endGO -- 调用函数select id, value =   dbo.f_value(id) from tb group by id-- 删除表drop table tb-- 删除函数drop function dbo.f_value-----------------------------------------------

只需要把里面的SQL语句改成你想要的就行了

SQL Server:

CREATE FUNCTION dbo.f_position(@ClassID BIGINT)RETURNS VARCHAR(8000)ASBEGIN DECLARE @str VARCHAR(8000)SET @str = ''SELECT @str = @str + (dbo.ufn_GetLocalizedCodeName([StevenhMembers].[Position], 'en-CA')) + '_' + isnull([StevenhMembers].[MemberName] ,'') + ',' FROM [ReadModel].[StevenhMembers] WHERE [StevenhMembers].[ClassID]=@ClassID AND dbo.ufn_GetLocalizedCodeName([StevenhMembers].[Position], 'en-CA') in ('Teacher', 'Monitor')ORDER BY PositionRETURN substring(@str,1,len(@str)-1)  --去除最后一个逗号ENDgoselect ClassID,MemberName = dbo.f_position(ClassID) from [Model].[StevenhMembers] WHERE dbo.ufn_GetLocalizedCodeName([StevenhMembers].[Position], 'en-CA') in ('Teacher', 'Monitor') GROUP BY ClassID

StevenhMembers表:

0 0