How to concatenate the one column rows and sum the other columns

Question

How to concatenate the one column rows and sum the other columns

s.chandrahasan

SSC Eights!

Points: 858
More actions
April 10, 2014 at 5:44 am

#290122

Hi All ,
table :
Subject mark1
--------- ------------
Matchs 90
Physics 85
English 50
I need to result in one row like below
concatenate the subjects and sum the marks
Subject Mark
--------- -------
Matchs,Physics,English 225
Advance Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

I have created a sample table and some sample data for you (please do this in the future). I also added a StudentID since you will probably have something like that in the actual table.create table dbo.SubjectMark (

StudentID int null

,Subject varchar(255) null

,Mark1 smallint null)

insert into dbo.SubjectMark

values (1,'Matchs',90)

,(1,'Physics',85)

,(1,'English',50);

SELECT

SubjectList = substring((SELECT ( ', ' + Subject )

FROM SubjectMark t2

WHERE t1.StudentID = t2.StudentID

ORDER BY

Subject

FOR XML PATH( '' )

), 3, 1000 )

,sum(Mark1)

FROM SubjectMark t1

group by StudentID

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 2

SET NOCOUNT on

IF OBJECT_ID('table1','u')IS NOT NULL

DROP TABLE table1

go

create table table1

(Subject varchar(10),

Mark int

)

go

insert into table1

values

('Matchs',90),

('Physics',85),

('English',50)

go

declare

@Subject varchar(1000) = '',

@mark-3 INT = 0

select

@Subject = @Subject + ',' + Subject,

@mark-3 = @mark-3 + Mark

from table1

SELECT RIGHT(@subject,LEN(@subject)-1), @mark-3

s.chandrahasan SSC Eights! Points: 858 More actions · Answer 3

Hi ,

Thanks for your reply 🙂

Now I have small issue , I have same subject with different marks (English subject ).

How do I concatenate distinct subjects with sum of all marks ?

table :

Subject mark1

--------- ------------

Matchs 90

Physics 85

English 50

English 46

English 78

English 67

I need to result in one row like below

concatenate the distinct subjects and sum the marks

Subject Mark

--------- -------

Matchs,Physics,English 416

How do I concatenate distinct subjects with sum of all marks ?[/b]

Advance Thanks

klini SSC-Addicted Points: 458 More actions · Answer 4

You can still use either suggestion above by feeding them a work table instead:

select Subject, sum(Mark) Mark

into table2

from table1

group by Subject

-- now you can use table2 wherever you saw table1 before