April 10, 2014 at 5:44 am
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
April 10, 2014 at 8:46 am
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
April 10, 2014 at 8:47 am
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,
from table1
SELECT RIGHT(@subject,LEN(@subject)-1), @mark-3
April 10, 2014 at 10:06 pm
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
April 11, 2014 at 10:01 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply