sql server - Dynamic SQL pivot -


i'm using following sql (with sql server 2016) pivot list of test results our lms (moodle):

select *       (select          u.firstname name,          u.lastname last_name,          u.idnumber id_number,          gi.itemname exam_name,          cast(gg.finalgrade / gi.grademax * 100 integer) grade                 mdl_grade_grades gg       inner join           mdl_grade_items gi on gg.itemid = gi.id       inner join           mdl_course c on gi.courseid = c.id       inner join           mdl_user u on gg.userid = u.id                (gi.itemname not null)           , (gi.courseid = 123)) source pivot      (max(grade)      exam_name in ([exam abc], [exam def], [exam ghi],                        [exam jkl], [exam xyz])     ) pivt order      id_number

the result be:

name    last name   id number   division    region      branch      exam abc    exam def    exam ghi    exam jkl    exam xyz john    tester      3343664     abc         west rgn    agy       65                                  44 kenny   quipton     4342423     dda         central     rgn frn     88          66          90                      89 molefi  manase      5456545     ccs         abc rgn     xxx sol     74          90          85          80          77

my issue exam names have hardcoded in for in(...) list, each time changes list has manually updated.

can sql rewritten using dynamic sql?

what better - using stuff() or for xml path, or else?

i can't use stored procedures (the application not support them).

this uses for xml should work same either way

declare @exams nvarchar(max), @sql nvarchar(max) set     @exams = select stuff(select ',',  + quotename(t.itemname)                          (                            select distinct gi.itemname                               mdl_grade_items gi                              gi.itemname not null , gi.courseid = 123                            order gi.itemname                           ) t               xml path(''), 0, 1, '')  set    @sql = n'     select *              (select            u.firstname name,            u.lastname last_name,            u.idnumber id_number,            gi.itemname exam_name,            cast(gg.finalgrade / gi.grademax * 100 integer) grade                      mdl_grade_grades gg          inner join             mdl_grade_items gi on gg.itemid = gi.id          inner join             mdl_course c on gi.courseid = c.id          inner join             mdl_user u on gg.userid = u.id                     (gi.itemname not null)             , (gi.courseid = 123)) source     pivot         (max(grade)          exam_name in (' + @exams + ')        ) pivt     order         id_number  '  exec sp_executesql @sql

Comments

Post a Comment

Popular posts from this blog

Is there a better way to structure post methods in Class Based Views -

i'm using class based views in django , never specify url in ajax calls , omit action parameter in form because know these requests go through post method of class based view. notice view - profile page make many requests depending on user updating on page - post method become convoluted , if/else mess example: def post(self, request, *args, **kwargs): # handle user changing available date if request.post.get('availabledate') != none: self.updateavailabledate(request) return jsonresponse({'result':'success'}) if request.post.get('newprojectname') != none: creative_user = creativeuserprofile.objects.get(id = request.user.id) project = project.create_project(creative_user, request.post['newprojectname']) project.save() return jsonresponse({'projectid': project.id }) if request.post.get('projectimage') != none , request.files['file']: proj...
Read more

performance - Why is XCHG reg, reg a 3 micro-op instruction on modern Intel architectures? -

i'm doing micro-optimization on performance critical part of code , came across sequence of instructions (in at&t syntax): add %rax, %rbx mov %rdx, %rax mov %rbx, %rdx i thought had use case xchg allow me shave instruction , write: add %rbx, %rax xchg %rax, %rdx however, dimay found agner fog's instruction tables , xchg 3 micro-op instruction 2 cycle latency on sandy bridge, ivy bridge, broadwell, haswell , skylake. 3 whole micro-ops , 2 cycles of latency! 3 micro-ops throws off 4-1-1-1 cadence , 2 cycle latency makes worse original in best case since last 2 instructions in original might execute in parallel. now... cpu might breaking instruction micro-ops equivalent to: mov %rax, %tmp mov %rdx, %rax mov %tmp, %rdx where tmp anonymous internal register , suppose last 2 micro-ops run in parallel latency 2 cycles. given register renaming occurs on these micro-architectures, though, doesn't make sense me done way. why wouldn't register renamer ...
Read more

c# - Asp.net web api : redirect unauthorized requst to forbidden page -

im trying redirect unauthorized request forbidden page instead i'm getting forbidden page in response body , how can fix ? here's startup class : app.createperowincontext(storecontext.create); app.createperowincontext<applicationusermanager>(applicationusermanager.create); app.createperowincontext<applicationsigninmanager>(applicationsigninmanager.create); app.usecookieauthentication(new cookieauthenticationoptions { authenticationtype = defaultauthenticationtypes.applicationcookie, expiretimespan = timespan.fromdays(30), }); app.useexternalsignincookie(defaultauthenticationtypes.externalcookie); app.usetwofactorsignincookie(defaultauthenticationtypes.twofactorcookie, timespan.fromminutes(5)); app.usetwofactorrememberbrowsercookie(defaultauthenticationtypes.twofactorrememberbrowsercookie); the method im trying reach : [httpget] [authorize(roles = "admin")] public string getcurrentusername() { return userman...
Read more