SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Conversion failed when converting the varchar valu
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shriroshanraj
Starting Member

30 Posts

Posted - 07/16/2013 :  07:51:04  Show Profile  Reply with Quote
DECLARE @AssignmentList varchar(3000),@Sql varchar(max)
SET @AssignmentList =STUFF((SELECT DISTINCT ',[' + assignmentname + ']' FROM assignment where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b' FOR XML PATH('')),1,1,'')

SET @Sql ='select *
from
(
select p.firstname +p.lastname as StudentName,a.assignmentname,cast((asg.pointsawarded+''/''+a.pointspossible )as int )as Marks
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid
inner join assignment a
on a.coursesectionid = cs.coursesectionid
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where cs.coursesectionid=''b78a6efe-ac77-4e49-806a-fc2fad71068b''
and cs.courserole=2
)t
PIVOT(SUM(Marks) FOR assignmentname IN(' + @AssignmentList + '))p'

execute(@sql)

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 07/16/2013 :  08:05:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You are using integer division. Try this
DECLARE @AssignmentList VARCHAR(3000),
	@Sql VARCHAR(MAX);

SET	@AssignmentList = STUFF((SELECT DISTINCT ',' + QUOTENAME(AssignmentName) FROM dbo.Assignment WHERE CourseSectionID = 'b78a6efe-ac77-4e49-806a-fc2fad71068b' ORDER BY ',' + QUOTENAME(AssignmentName) FOR XML PATH('')), 1, 1, '');

SET	@Sql = 'SELECT	*
		from	(
				SELECT		p.FirstName + p.LastName AS StudentName,
						a.AssignmentName,
						CAST(100E * asg.PointsAwarded / a.PointsPossible) AS INT) AS Marks
				FROM		dbo.Person2 AS p
				INNER JOIN	dbo.CourseSectionRoster AS cs ON cs.PersonID = p.PersonID 
				INNER JOIN	dbo.Assignment AS a ON a.CourseSectionID = cs.CourseSectionID
				LEFT JOIN	dbo.AssignmentSubmission AS asg ON asg.AssignmentID = a.AssignmentID
				WHERE		cs.CoursesectionID = ''b78a6efe-ac77-4e49-806a-fc2fad71068b''
						AND cs.CourseRole = 2
			) AS t
		PIVOT	(
				SUM(Marks)
				FOR AssignmentName IN (' + @AssignmentList + ')
			) AS p';

EXEC	(@Sql);



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/16/2013 :  08:15:00  Show Profile  Reply with Quote
it is not integer division. I need to display slash between two integers. the output should be 10/20

Edited by - shriroshanraj on 07/16/2013 08:15:28
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 07/16/2013 :  08:26:02  Show Profile  Reply with Quote
quote:
Originally posted by shriroshanraj

it is not integer division. I need to display slash between two integers. the output should be 10/20

If you want to display the slash, then the data type cannot be INT when you compute Marks column. Make it VARCHAR or another character type. But then, you cannot add them up in the PIVOT clause.
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/18/2013 :  02:02:16  Show Profile  Reply with Quote
if i need slash there, how do i change the query
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/18/2013 :  02:06:41  Show Profile  Reply with Quote

DECLARE @AssignmentList varchar(3000),@Sql varchar(max)
SET @AssignmentList =STUFF((SELECT DISTINCT ',[' + assignmentname + ']' FROM assignment where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b' FOR XML PATH('')),1,1,'')

SET @Sql ='select *
from
(
select p.firstname +p.lastname as StudentName,a.assignmentname,cast((asg.pointsawarded+''/''+a.pointspossible )as varchar(20))as Marks
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid 
inner join assignment a
on a.coursesectionid = cs.coursesectionid
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where cs.coursesectionid=''b78a6efe-ac77-4e49-806a-fc2fad71068b''
and cs.courserole=2
)t
PIVOT(SUM(Marks) FOR assignmentname IN(' + @AssignmentList + '))p'

execute(@sql)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/18/2013 :  02:11:03  Show Profile  Reply with Quote
i am getting error
Operand data type varchar is invalid for sum operator.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 07/18/2013 :  02:12:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE @AssignmentList VARCHAR(3000),
	@Sql VARCHAR(MAX);

SET	@AssignmentList = STUFF((SELECT DISTINCT ',' + QUOTENAME(AssignmentName) FROM dbo.Assignment WHERE CourseSectionID = 'b78a6efe-ac77-4e49-806a-fc2fad71068b' ORDER BY ',' + QUOTENAME(AssignmentName) FOR XML PATH('')), 1, 1, '');

SET	@Sql = 'SELECT	*
		from	(
				SELECT		p.FirstName + p.LastName AS StudentName,
						a.AssignmentName,
						CAST(asg.PointsAwarded AS VARCHAR(12)) + ''/'' + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks
				FROM		dbo.Person2 AS p
				INNER JOIN	dbo.CourseSectionRoster AS cs ON cs.PersonID = p.PersonID 
				INNER JOIN	dbo.Assignment AS a ON a.CourseSectionID = cs.CourseSectionID
				LEFT JOIN	dbo.AssignmentSubmission AS asg ON asg.AssignmentID = a.AssignmentID
				WHERE		cs.CoursesectionID = ''b78a6efe-ac77-4e49-806a-fc2fad71068b''
						AND cs.CourseRole = 2
			) AS t
		PIVOT	(
				MAX(Marks)
				FOR AssignmentName IN (' + @AssignmentList + ')
			) AS p';

EXEC	(@Sql);



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/18/2013 :  02:17:29  Show Profile  Reply with Quote
quote:
Originally posted by shriroshanraj

i am getting error
Operand data type varchar is invalid for sum operator.


Oh Yes..You need to do cast before concatenation. Just didnt realize that when I posted.
Please use latest posted suggestion by Swepeso

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/18/2013 :  02:21:04  Show Profile  Reply with Quote
only PointsAwarded is displaying , but i need to display PointsAwarded / PointsPossible under assignmentname
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/18/2013 :  02:30:45  Show Profile  Reply with Quote
quote:
Originally posted by shriroshanraj

only PointsAwarded is displaying , but i need to display PointsAwarded / PointsPossible under assignmentname


Does PointsPossible have valid values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/18/2013 :  02:35:19  Show Profile  Reply with Quote
s PointsPossible has valid values
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/18/2013 :  02:57:18  Show Profile  Reply with Quote
Run this query first and post back result

SELECT	TOP 10	asg.PointsAwarded,a.PointsPossible ,
						CAST(asg.PointsAwarded AS VARCHAR(12)) + ''/'' + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks
				FROM		dbo.Person2 AS p
				INNER JOIN	dbo.CourseSectionRoster AS cs ON cs.PersonID = p.PersonID 
				INNER JOIN	dbo.Assignment AS a ON a.CourseSectionID = cs.CourseSectionID
				LEFT JOIN	dbo.AssignmentSubmission AS asg ON asg.AssignmentID = a.AssignmentID
				WHERE		cs.CoursesectionID = 'b78a6efe-ac77-4e49-806a-fc2fad71068b'
						AND cs.CourseRole = 2



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 07/18/2013 03:00:56
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/18/2013 :  03:05:58  Show Profile  Reply with Quote
i changed like this, bcoz it shows error

CAST(asg.PointsAwarded AS VARCHAR(12)) + '/' + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks

marks displays null value.

in my db PointsAwarded has null value. PointsPossible has value.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/18/2013 :  03:10:16  Show Profile  Reply with Quote
quote:
Originally posted by shriroshanraj

i changed like this, bcoz it shows error

CAST(asg.PointsAwarded AS VARCHAR(12)) + '/' + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks

marks displays null value.

in my db PointsAwarded has null value. PointsPossible has value.



COALESCE(CAST(asg.PointsAwarded AS VARCHAR(12)) + '/','') + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/18/2013 :  03:13:19  Show Profile  Reply with Quote
i changed like this

ISNULL(CAST(asg.PointsAwarded AS VARCHAR(12)) ,0) +'/' + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks

Marks displays 0/12
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/18/2013 :  03:14:57  Show Profile  Reply with Quote
If i use this

COALESCE(CAST(asg.PointsAwarded AS VARCHAR(12)) + '/','') + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks

i get only PointsPossible value.
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/18/2013 :  03:20:14  Show Profile  Reply with Quote
from the below query , i need to display the total number of pointswarded/total number of pointspossible under totalpoints.how to get the totalpoints.

the output should be

StudentName Totalpoints network testing createnew newtest
Raj Kumar 0/105 0/20 0/30 0/30 0/25

DECLARE @AssignmentList VARCHAR(3000),
@Sql VARCHAR(MAX);

SET @AssignmentList = STUFF((SELECT DISTINCT ',' + QUOTENAME(AssignmentName) FROM dbo.Assignment WHERE CourseSectionID = 'b78a6efe-ac77-4e49-806a-fc2fad71068b' ORDER BY ',' + QUOTENAME(AssignmentName) FOR XML PATH('')), 1, 1, '');

SET @Sql = 'SELECT *
from (
SELECT p.FirstName + p.LastName AS StudentName,
a.AssignmentName,
ISNULL(CAST(asg.PointsAwarded AS VARCHAR(12)) ,0) +''/'' + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks
FROM dbo.Person2 AS p
INNER JOIN dbo.CourseSectionRoster AS cs ON cs.PersonID = p.PersonID
INNER JOIN dbo.Assignment AS a ON a.CourseSectionID = cs.CourseSectionID
LEFT JOIN dbo.AssignmentSubmission AS asg ON asg.AssignmentID = a.AssignmentID
WHERE cs.CoursesectionID = ''b78a6efe-ac77-4e49-806a-fc2fad71068b''
AND cs.CourseRole = 2
) AS t
PIVOT (
MAX(Marks)
FOR AssignmentName IN (' + @AssignmentList + ')
) AS p';

EXEC (@Sql);

Edited by - shriroshanraj on 07/18/2013 03:26:33
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/18/2013 :  04:31:06  Show Profile  Reply with Quote

DECLARE @AssignmentList VARCHAR(3000),
@Sql VARCHAR(MAX);

SET @AssignmentList = STUFF((SELECT DISTINCT ',' + QUOTENAME(AssignmentName) FROM dbo.Assignment WHERE CourseSectionID = 'b78a6efe-ac77-4e49-806a-fc2fad71068b' ORDER BY ',' + QUOTENAME(AssignmentName) FOR XML PATH('')), 1, 1, '');

SET @Sql = 'SELECT *
from (
SELECT p.FirstName + p.LastName AS StudentName,
a.AssignmentName,
ISNULL(CAST(asg.PointsAwarded AS VARCHAR(12)) ,0) +''/'' + CAST(a.PointsPossible AS VARCHAR(12)) AS Marks,
ISNULL(CAST(SUM(asg.PointsAwarded) OVER (PARTITION BY p.FirstName,p.LastName) AS VARCHAR(12)) ,0) +''/'' + CAST(SUM(a.PointsPossible) OVER (PARTITION BY p.FirstName,p.LastName) AS VARCHAR(12)) AS TotalMarks
FROM dbo.Person2 AS p
INNER JOIN dbo.CourseSectionRoster AS cs ON cs.PersonID = p.PersonID 
INNER JOIN dbo.Assignment AS a ON a.CourseSectionID = cs.CourseSectionID
LEFT JOIN dbo.AssignmentSubmission AS asg ON asg.AssignmentID = a.AssignmentID
WHERE cs.CoursesectionID = ''b78a6efe-ac77-4e49-806a-fc2fad71068b''
AND cs.CourseRole = 2
) AS t
PIVOT (
MAX(Marks)
FOR AssignmentName IN (' + @AssignmentList + ')
) AS p';

EXEC (@Sql);


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000