如何以逗号分隔的方式将多行数据显示到单个单元格中

My requirement is to display multiple rows data into a single cell. For example I have a teacher who is specialized in multiple subjects.

我的要求是将多行数据显示在一个单元格中。例如,我有一位专门研究多个科目的老师。

staffid    Subjects
-------------------   
13         Hindi
13         asd

I wants result in following format

我希望得到以下格式的结果

Hindi, asd

for staffid 13.

对于职员13。

To do this task I used following code

为了完成这个任务,我使用了以下代码

declare @output varchar(max)
select @output = COALESCE(@output + ', ', '') + sr.title
from streamsubjects sr
join StaffSubjectAssociation ir on ir.StreamSubjectID=sr.StreamSubjectID
where StaffId = 13

select @output

To get desired output I created one user defined scalar function which is given below

为了获得所需的输出,我创建了一个用户定义的标量函数,如下所示

   ALTER FUNCTION [dbo].[getSubjectsForStaff]
   (
    @StaffId int 
   )
    RETURNS varchar
    AS
    BEGIN

   declare @output varchar(max)
   select @output = COALESCE(@output + ', ', '') + sr.title
   from streamsubjects sr
 join StaffSubjectAssociation ir on ir.StreamSubjectID=sr.StreamSubjectID
  where StaffId = @StaffId

  RETURN @output
END

But I am not getting desired result I am only getting first alphabet of subject. Can anyone tell me why I am not getting desired result using same code in scalar function.

但我没有得到理想的结果我只得到第一个主题字母。任何人都可以告诉我为什么我没有在标量函数中使用相同的代码获得所需的结果。

What will be the correct solution, to achieve result? Please help me I am new in this technology.

实现结果的正确解决方案是什么?请帮助我,我是这项技术的新手。

3 个解决方案

#1

Also try this method :

也尝试这种方法:

DECLARE @table TABLE(staffid INT, subject VARCHAR(30))
INSERT INTO @table
VALUES
(13,'Hindi'),
(13,'English'),
(14,'Japanese'),
(14,'English')

SELECT staffid,
       STUFF(grp, 1, 1, '')
FROM   @table a
       CROSS APPLY (SELECT ',' + subject
                    FROM   @table b
                    WHERE  a.staffid = b.staffid
                    FOR XML PATH('')) group_concat(grp)
GROUP  BY staffid,grp 

#2

Your query and function seems to be perfect. You just need to give a size to your function RETURN.

您的查询和功能似乎是完美的。你只需要给你的函数RETURN一个大小。

   ALTER FUNCTION [dbo].[getSubjectsForStaff]
   (
    @StaffId int 
   )
    RETURNS varchar(MAX) -- Add the size here
    AS
    BEGIN

   declare @output varchar(max)
   select @output = COALESCE(@output + ', ', '') + sr.title
   from streamsubjects sr
 join StaffSubjectAssociation ir on ir.StreamSubjectID=sr.StreamSubjectID
  where StaffId = @StaffId

  RETURN @output
  END

#3

same as @Deepak Pawar variant, but without cross apply

与@Deepak Pawar变体相同,但没有交叉应用

DECLARE @table TABLE
    (
      staffid INT ,
      [subject] VARCHAR(30)
    )
INSERT  INTO @table
VALUES  ( 13, 'Hindi' ),
        ( 13, 'English' ),
        ( 14, 'Japanese' ),
        ( 14, 'English' )

SELECT DISTINCT
        a.staffid ,
        SUBSTRING(( SELECT  ', ' + b.[subject]
                    FROM    @table b
                    WHERE   a.staffid = b.staffid
                  FOR
                    XML PATH('')
                  ), 3, 999) grp
FROM    @table a

output result