CODEWITHSUNDEEP
sqlsql-servert-sql
Piyush Srivastava
Piyush Srivastava

Reputation: 35

How to display multiple row data into a single cell with comma separated

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.

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.

Upvotes: 2

Views: 178

Answers (3)

Vasily
Vasily

Reputation: 5782

same as @Deepak Pawar variant, but without cross apply

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

enter image description here

Upvotes: 0

Deep
Deep

Reputation: 3202

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

Upvotes: 1

sqluser
sqluser

Reputation: 5672

Your query and function seems to be perfect. You just need to give a size to your function 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

Upvotes: 0

Related Questions