Combine two rows into one row. like average function but instead it will concat

Question

I'm using SQL Server 2000.

What I want to do is like average function but instead it will concat.

Is there a way that I can do that?

For example I have this data.

Name   |   Score
Name1  |   50
Name1  |   70

and the output should be like this.

Name  |  Score
Name1 |  50,70

Answer 1

Use below query for your reference.

Query

Select main.doctorID,
       Left(Main.submain,Len(Main.submain)-1) As 'Title'
From
    (
        Select distinct ST2.doctorID, 
            (
                Select convert(varchar,ST1.encounterid) + ',' AS [text()]
                From dbo.enc ST1
                Where ST1.doctorID = ST2.doctorID
                ORDER BY ST1.doctorID
                For XML PATH ('')
            ) submain
        From dbo.enc ST2
    ) [Main]

Answer 2

If you can use CLR, look at this example :

https://msdn.microsoft.com/en-us/library/ms165055%28v=vs.90%29.aspx

It provides a custom aggregate that concatenates values, which results in very clean code.

Answer 3

You can do it by this simple query.

Select Name, (Select SUBSTRING((SELECT ', '+Score from TableName for XML 
Path('')) ,2,8000)) from TableName

You will have result like

ColumnName | val1, val2, ....

Answer 4

Query

SELECT Name, 
(SELECT SUBSTRING((SELECT ', '+CAST(Score AS VARCHAR(MAX)) FROM my_table FOR XML Path('')) ,2,1000)) AS Score 
FROM my_table
GROUP BY name;

Fiddle for reference

If you are using SQL Server 2000, then try to create a function as follows.

CREATE TABLE my_table(name VARCHAR(50),score INT);

INSERT INTO my_table VALUES('Name1',50);
INSERT INTO my_table VALUES('Name1',70);

Function

CREATE FUNCTION commaseparated(@name VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @score VARCHAR(MAX)
SELECT @score = COALESCE(@score + ', ', '') + CAST(score AS VARCHAR(MAX))
FROM my_table
WHERE name = @name 
RETURN @score
END

SELECT 
name, 
score = dbo.commaseparated(name) 
FROM my_table 
GROUP BY name;

Answer 5

check this.

INSERT INTO @T VALUES
    ('name1', 50),
    ('name1', 70)


SELECT * FROM @T

Select name ,
STUFF((SELECT ',' + cast( score as varchar(50)) FROM @T WHERE (
name=Result.name) FOR XML PATH ('')),1,1,'') AS BATCHNOLIST
From @T AS Result
GROUP BY name

Can I Comma Delimit Multiple Rows Into One Column?