Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#4.0
Hi friends,
This is my query
 
alter procedure sp_getmarkforclass(@classid int,@examid int)
as
begin
declare @paramlist varchar(max),@query nvarchar(max)
set @paramlist=STUFF((select distinct ',[' + SubjectId + ']' from School.dbo.Mark_details for xml path('')),1,1,'')
--print @paramlist
set @query='select' + @paramlist + 'from (select * from Mark_details)src pivot(sum(MarkObtained) for SubjectId in (' + @paramlist + ')) pvt'
exec @query
end
MarkId	ExamId	Subid  admnumberMark	Mm	Grade	classid
66	6	23011	103	77	100	NULL	12
67	6	23012	103	88	100	NULL	12
68	6	23013	103	77	100	NULL	12
69	6	23014	103	77	100	NULL	12
70	6	23015	103	66	100	NULL	12
71	6	23011	102	77	100	NULL	12
72	6	23012	102	88	100	NULL	12
73	6	23013	102	99	100	NULL	12
74	6	23014	102	99	100	NULL	12
75	6	23015	102	77	100	NULL	12
76	6	23011	100	77	100	NULL	12
77	6	23012	100	77	100	NULL	12
78	6	23013	100	88	100	NULL	12
79	6	23014	100	99	100	NULL	12
80	6	23015	100	88	100	NULL	12
 
but the error is
The name 'select[23011],[23012],[23013],[23014],[23015]from (select * from Mark_details)src pivot(sum(MarkObtained) for SubjectId in ([23011],[23012],[23013],[23014],[23015])) pvt' is not a valid identifier.
 
i want result as'
 
studentname rollnumber tamil(23011) english maths science socialscience
XXXXXX 102 77 88 99 99 77
 
like above for class and exam wise.here i just change markobtained as mm but same column name used to the query
Posted 27-Dec-12 3:18am
Edited 27-Dec-12 3:20am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

To fix that I would probably run the first query to see what answer you get there and adjust to the needed result, then you can correct the dynamic portion which is failing.
 
Which would be:
 
SELECT DISTINCT subjectId from
(
    SELECT * From school.dbo.Mark_details
    SUM(MarkObtained)
)
 
 
which may require some adjusting but that can direct where the values are coming from, then adjust your dynamic and pivot from there.
 
If you want to run that as a script and trouble shoot from there you can use the following:
--alter procedure sp_getmarkforclass(@classid int,@examid int)
--as
--begin
declare @paramlist varchar(max),@query nvarchar(max)
set @paramlist=STUFF((select distinct ',[' + SubjectId + ']' from School.dbo.Mark_details for xml path('')),1,1,'')
--print @paramlist
set @query='select' + @paramlist + 'from (select * from Mark_details)src pivot(sum(MarkObtained) for SubjectId in (' + @paramlist + ')) pvt'
exec @query
--end
 
And can run it as a script to trouble shoot that SP.
 
Hope that helps
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 George Jonsson 215
1 Kornfeld Eliyahu Peter 169
2 Zoltán Zörgő 139
3 PIEBALDconsult 130
4 OriginalGriff 120
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,107
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,342


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 27 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100