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

jquery - Responsive Navbar with Sub Navbar -

good day, forgive me nav vocabulary not best. if question confusing apologize. i'm working on navigation bar needs centered on page @ 768px , above. items in nav open (or perhaps toggle) subnav lives directly below. these nav should appear centered on page. i have been able work through responsive portion of nav bar, having main items align vertically , sub nav menu of each item roll out below, , gave them indent separation. working well. however, issue not responsive side, behavior @ width greater 768px. happens toggling menu's sub navigation pushes other main items down page below sub nav. here's code: $(document).ready(function () { //stick in fixed 100% height behind navbar don't wrap $('#slide-nav.navbar-inverse').after($('<div class="inverse" id="navbar-height-col"></div>')); $('#slide-nav.navbar-default').after($('<div id="navbar-height-col"></d
Read more