PivotTable Query of Death

death.sql

SELECT Indicator.aggregation     c1, 
       Sum(V.value)              c2, 
       Avg(V.value)              c3, 
       AdminLevel1.adminentityid c4, 
       AdminLevel1.name          c5, 
       partner.partnerid         c6, 
       partner.name              c7, 
       activity.databaseid       c8, 
       userdatabase.name         c9, 
       Indicator.indicatorid     c10, 
       Indicator.name            c11, 
       Indicator.sortorder       c12, 
       activity.activityid       c13, 
       activity.name             c14, 
       activity.sortorder        c15 
FROM   indicatorvalue V 
       LEFT JOIN reportingperiod Period 
              ON ( Period.reportingperiodid = V.reportingperiodid ) 
       LEFT JOIN (SELECT indicatorid SourceId, 
                         indicatorid, 
                         name, 
                         sortorder, 
                         aggregation, 
                         activityid 
                  FROM   indicator 
                  WHERE  datedeleted IS NULL 
                         AND indicatorid IN ( 275, 274, 278, 277, 
                                              276, 1894, 1134, 305, 
                                              1135, 304, 1132, 1133, 
                                              1130, 309, 1131, 311, 
                                              1129, 310, 313, 312, 
                                              4041, 314, 317, 319, 
                                              318, 296, 1141, 1140, 
                                              1139, 1138, 1137, 302, 
                                              1136, 303, 342, 341, 
                                              338, 344, 326, 327, 
                                              322, 323, 334, 1040, 
                                              335, 332, 333, 330, 
                                              328, 329, 119, 118, 
                                              125, 124, 123, 122, 
                                              121, 953, 948, 129, 
                                              949, 1503, 950, 1500, 
                                              946, 947, 943, 942, 
                                              937, 938, 933, 932, 
                                              934, 929, 928, 931, 
                                              930, 926, 927, 652, 
                                              649, 1520, 1521, 2102, 
                                              651, 446, 1522, 650, 
                                              447, 2105, 2111, 1258, 
                                              2108, 1510, 2086, 1504, 
                                              1507, 2085, 1517, 1519, 
                                              1518, 1512, 1515, 2093, 
                                              747, 2133, 746, 745, 
                                              744, 2130, 738, 737, 
                                              2143, 736, 2142, 743, 
                                              742, 2136, 741, 465, 
                                              2117, 2114, 455, 2124, 
                                              452, 2127, 451, 450, 
                                              449, 448, 2148, 2149, 
                                              2146, 2147, 2144, 735, 
                                              2145, 2156, 960, 2152, 2153 ) 
                  UNION ALL 
                  SELECT L.sourceindicatorid SourceId, 
                         D.indicatorid, 
                         D.name, 
                         D.sortorder, 
                         D.aggregation, 
                         D.activityid 
                  FROM   indicator D 
                         INNER JOIN indicatorlink L 
                                 ON ( D.indicatorid = L.destinationindicatorid ) 
                         INNER JOIN indicator S 
                                 ON ( S.indicatorid = L.sourceindicatorid ) 
                  WHERE  D.datedeleted IS NULL 
                         AND S.datedeleted IS NULL 
                         AND D.indicatorid IN ( 275, 274, 278, 277, 
                                                276, 1894, 1134, 305, 
                                                1135, 304, 1132, 1133, 
                                                1130, 309, 1131, 311, 
                                                1129, 310, 313, 312, 
                                                4041, 314, 317, 319, 
                                                318, 296, 1141, 1140, 
                                                1139, 1138, 1137, 302, 
                                                1136, 303, 342, 341, 
                                                338, 344, 326, 327, 
                                                322, 323, 334, 1040, 
                                                335, 332, 333, 330, 
                                                328, 329, 119, 118, 
                                                125, 124, 123, 122, 
                                                121, 953, 948, 129, 
                                                949, 1503, 950, 1500, 
                                                946, 947, 943, 942, 
                                                937, 938, 933, 932, 
                                                934, 929, 928, 931, 
                                                930, 926, 927, 652, 
                                                649, 1520, 1521, 2102, 
                                                651, 446, 1522, 650, 
                                                447, 2105, 2111, 1258, 
                                                2108, 1510, 2086, 1504, 
                                                1507, 2085, 1517, 1519, 
                                                1518, 1512, 1515, 2093, 
                                                747, 2133, 746, 745, 
                                                744, 2130, 738, 737, 
                                                2143, 736, 2142, 743, 
                                                742, 2136, 741, 465, 
                                                2117, 2114, 455, 2124, 
                                                452, 2127, 451, 450, 
                                                449, 448, 2148, 2149, 
                                                2146, 2147, 2144, 735, 
                                                2145, 2156, 960, 2152, 2153 )) 
                 AS 
       Indicator 
              ON ( Indicator.sourceid = V.indicatorid ) 
       LEFT JOIN site Site 
              ON ( Period.siteid = site.siteid ) 
       LEFT JOIN partner Partner 
              ON ( site.partnerid = partner.partnerid ) 
       LEFT JOIN project Project 
              ON ( site.projectid = project.projectid ) 
       LEFT JOIN location Location 
              ON ( location.locationid = site.locationid ) 
       LEFT JOIN activity Activity 
              ON ( activity.activityid = Indicator.activityid ) 
       LEFT JOIN userdatabase UserDatabase 
              ON ( activity.databaseid = userdatabase.databaseid ) 
       LEFT JOIN (SELECT L.locationid, 
                         E.adminentityid, 
                         E.name 
                  FROM   locationadminlink L 
                         LEFT JOIN adminentity E 
                                ON ( L.adminentityid = E.adminentityid ) 
                  WHERE  E.adminlevelid = 1) AS AdminLevel1 
              ON ( location.locationid = AdminLevel1.locationid ) 
WHERE  ( ( V.value <> 0 
           AND Indicator.aggregation = 0 ) 
          OR Indicator.aggregation = 1 ) 
       AND site.datedeleted IS NULL 
       AND activity.datedeleted IS NULL 
       AND userdatabase.datedeleted IS NULL 
       AND ( userdatabase.owneruserid = 167 
              OR 167 IN (SELECT p.userid 
                         FROM   userpermission p 
                         WHERE  p.allowview 
                                AND p.userid = 167 
                                AND p.databaseid = userdatabase.databaseid) ) 
       AND Period.date2 >= '2012-02-01' 
       AND Period.date2 <= '2012-10-05' 
GROUP  BY Indicator.indicatorid, 
          Indicator.aggregation, 
          AdminLevel1.adminentityid, 
          AdminLevel1.name, 
          partner.partnerid, 
          partner.name, 
          activity.databaseid, 
          userdatabase.name, 
          Indicator.indicatorid, 
          Indicator.name, 
          Indicator.sortorder, 
          activity.activityid, 
          activity.name, 
          activity.sortorder