sql function to combine values into one string

Question

I have a question on regarding the following post, I would add to it but it won't let me since I am new.

Edited to add the specific type of tables and better information

I have two tables below and would like the accomplish in getting tblnames of studentname into tblCombineNames into Student names.

Please advise, thanks!

TblNames

ID(PK)          StudentType(FK) StudentNo(FK)   GradeNo(FK)     StudentName  
----------      ----------      ----------      ----------      -------------
1               1               1               1               Mary         
2               1               1               1               John         
3               1               1               1               Sam          
4               2               2               2               Alaina       
5               2               2               2               Edward       
6               2               2               2               Joe

I would like the output to be below

TblCombineNames

ID(PK)          StudentType(PK) StudentNo(PK)   GradeNo(PK)     StudentNames       
----------      ----------      ----------      ----------      -------------      
1               1               1               1               Mary, John, Sam    
2               2               2               2               Alaina, Edward, Joe

I would have a scalar-valued Function named something like

---dbo.fn_Concatenate_Names
ALTER FUNCTION [dbo].[fn_Concatenate_Names]
(
    @StudentType VARCHAR(250),
    @StudentNo VARCHAR(250),
    @GradeNo VARCHAR(250)
)
RETURNS Varchar(250)
BEGIN
Declare @rtn Varchar(250)

BEGIN
    Select @rtn=(
    Select StudentNames + ', ' as 'data()'
    from tblStudentnames    
    where studentType = @StudentType and StudentNo = @StudentNo and GradeNo = @GradeNo
    for XML path('')
    )

    Set @rtn = LEFT(@rtn, Len(@rtn) - 1)
    END
    RETURN (@rtn)
END

I would do on update like to call the function

update tblCombineNames
set studentnames = fn_concatenate_names(StudentType,StudentNo,GradeNo)

It seems like it would work but it takes 2 hours to run on tblStudentNames of 250730 records. I don't think it should take that long.

the post here is getting very close, I need to figure out on how to use it in a function. Click to take a look at the post I am talking about. — eripey
– eripey, Commented Mar 29, 2013 at 20:36
Okay, I probably did the long way but it works and it's quick too! I ended up import the tblNames into a temp table while using the xml path way, once the names were combined in the temp db, then I did an inner join with the temp and tblCombineNames with an update and then ended doing a LEft Funtion -1 to take out the extra comma at the end of each value in tblCombine names. I got close with JW and even closer to the above link I posted! Thanks everyone! — eripey
– eripey, Commented Mar 29, 2013 at 23:22

John Woo · Accepted Answer · 2013-03-29 05:34:00Z

1

You can simply put it inside a subquery and JOIN it with the table, ex

UPDATE  a
SET     a.names = b.StudentsList
FROM    tableName a
        INNER JOIN
        (
            SELECT  ST2.SubjectID, 
                    substring((SELECT ','+ ST1.StudentName
                            FROM dbo.Students ST1
                            WHERE ST1.SubjectID = ST2.SubjectID
                            ORDER BY ST1.SubjectID
                            For XML PATH ('')),2, 1000
                            ) StudentsList
            FROM    dbo.Students ST2
            GROUP   BY ST2.SubjectID
        ) b ON a.SubjectID = b.SubjectID
WHERE   a.SubjectID = @subjectid

answered Mar 29, 2013 at 5:34

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

eripey Over a year ago

J W, Can you look at my original post, I have included tables to give a better example. I am at lost with this on why the update is taking so long.

John Woo Over a year ago

Do you have index set on column subjectID?

eripey Over a year ago

I am not sure what you mean that, if you mean going into table design and yes to indexable, yes it is. Do you mind at looking my post again, i edited to post to something more useful that might make more sense, please advise, thanks!

Collectives™ on Stack Overflow

sql function to combine values into one string

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related