sql server - Error in Dynamic PIVOT Table in SQL -

-

i getting error in dynamic pivot table.

i have tables tt_child 

ttchild_id      tt_id       roll_no     std_reg_id      attendance 1             3               1         22                     1 2             3               2         23                     0

and table

tt_master 

tt_id   attend_date  time_from  time_to   course_id  faculty_id  acad_year  subject_id     1   2014-03-01  10:00   11:00       1         16     2013-2014     34     2   2014-03-02  10:00   11:00       1         16     2013-2014        34     3   2014-03-03  10:00   11:00       1         16     2013-2014    34

student_registration_master

std_reg_id  stud_fname  stud_mname  stud_mname

--i using pivot query getting error. have use attend_date , time column name.

create procedure [dbo].[attendance_test]     @courseid int=null, @acadyear nvarchar(15)=null  declare @collist varchar(max) declare @qry varchar(max)  set @collist = stuff((select distinct ',' + quotename(convert(varchar(19), ttm.attend_date, 103)) + ' '+ quotename(ttm.time_from)              tt_child sa              inner join tt_master ttm on sa.tt_id = ttm.tt_id              (ttm.course_id = @courseid)              xml path(''), type).value('/', 'nvarchar(max)') ,1,1,'')  set @qry = 'select sa.reg_id, stud_fname +  stud_mname + stud_lname [student name], '+@collist+'           (         select sa.reg_id, sr.stud_fname, sr.stud_mname, sr.stud_lname, ttm.attend_date , sa.attendance tt_child sa         inner join tt_master ttm on sa.tt_id = ttm.tt_id         inner join student_registration_master sr on cr.reg_id = sr.stud_reg_id         (ttm.course_id = '+cast(@courseid varchar(50))+ ') , (ttm.acad_year = '''+@acadyear+''')         group sa.reg_id, sr.stud_fname, sr.stud_mname, sr.stud_lname, ttm.attend_date, sa.attendance      ) s     pivot     (         max(attendance) attend_date in (' + @collist + ')     ) pvt' print(@qry) exec(@qry)

-- getting error message

select sa.reg_id, stud_fname +  stud_mname + stud_lname [student name], [01/03/2014] [10:00:00.0000000],[02/03/2014] [10:00:00.0000000],[03/03/2014] [10:00:00.0000000],[05/03/2014] [10:00:00.0000000],[05/03/2014] [11:00:00.0000000]          (        select sa.reg_id, sr.stud_fname, sr.stud_mname, sr.stud_lname, ttm.attend_date , sa.attendance tt_child sa         inner join tt_master ttm on sa.tt_id = ttm.tt_id         inner join student_registration_master sr on cr.reg_id = sr.stud_reg_id         (ttm.course_id = 1) , (ttm.acad_year = '2013-2014')         group sa.reg_id, sr.stud_fname, sr.stud_mname, sr.stud_lname, ttm.attend_date, sa.attendance      ) s    pivot     (        max(attendance) attend_date in ([01/03/2014] [10:00:00.0000000],[02/03/2014] [10:00:00.0000000],[03/03/2014] [10:00:00.0000000],[05/03/2014] [10:00:00.0000000],[05/03/2014] [11:00:00.0000000])     ) pvt

error message

msg 102, level 15, state 1, line 12 incorrect syntax near '10:00:00.0000000'.

plz give solution

when review line of code see error:

max(attendance)  attend_date in ([01/03/2014] [10:00:00.0000000],[02/03/2014] [10:00:00.0000000],                     [03/03/2014] [10:00:00.0000000],[05/03/2014] [10:00:00.0000000],                     [05/03/2014] [11:00:00.0000000])

the date , time have each been wrapped in square brackets separately error.

you need concatenate date , time first, wrap in square brackets using quotename. should able change code to:

set @collist    = stuff((select distinct ',' + quotename(convert(varchar(19), ttm.attend_date, 103) + ' '+ ttm.time_from)              tt_child sa              inner join tt_master ttm on sa.tt_id = ttm.tt_id              (ttm.course_id = @courseid)              xml path(''), type).value('/', 'nvarchar(max)') ,1,1,'')

see sql fiddle demo of version , new version.


Comments

Post a Comment

Popular posts from this blog

c# - How to get the current UAC mode -

-
can check current uac type set in device before running application through c#. i got know approach check whether current user administrator or not. but, need know uac type set (i.e. default/never notify) internal logic. is possible? please help. this article self-elevation , checking level program runs in. due security reasons i'm afraid won't able retrieve uac level set. http://code.msdn.microsoft.com/windowsdesktop/csuacselfelevation-644673d3
Read more

postgresql - Lazarus + Postgres: incomplete startup packet -

-
i've been building lazarus pascal program using postgresql on back-end. used work fine following lines opening db. dbconn:= tpqconnection.create(nil); dbconn.hostname := 'localhost'; dbconn.databasename:= 'dbhrs'; dbconn.username:='mizk'; dbconn.password:='123'; dbconn.open; if dbconn.connected openhrsdb := true else openhrsdb := false; but moment change localhost server's ip (on lan), program stops. no errors or warnings. have no clue happening. here related details may narrow down problem. the program i'm running workstation on same lan server. both machines run ubuntu 12.04 desktop i can access postgres db on server using pgadmin iii, guess it's not firewall issue. i have allowed postgresql ufw , ufw disabled. strangely, when firewall disabled, can ssh server terminal using workstation. when it's enabled, can't my biggest concern right why pascal program not working when i...
Read more

javascript - Ajax jqXHR.status==0 fix error -

-
$.ajax({ url: urlstring, datatype: "json", type: "get", success: function (data) { alert(data); }, error: function (jqxhr, exception) { if (jqxhr.status === 0) { alert('not connect.\n verify network.'); } else if (jqxhr.status == 404) { alert('requested page not found. [404]'); } else if (jqxhr.status == 500) { alert('internal server error [500].'); } else if (exception === 'parsererror') { alert('requested json parse failed.'); } else if (exception === 'timeout') { alert('time out error.'); } else if (exception === 'abort') { alert('ajax request aborted.'); } else { alert('uncaught error.\n' + jqxhr.responsete...
Read more