1

I'm trying to build a pivot request but i don't undestand how it's applicable for my database. The value I need has to be calculate (A percentage) for each column and row. I'm using SQL Server 2012. My model contains three tables :

  • Qualification : Contains all the qualification (by section)
  • Module : Contains all the modules link to one qualification
  • Module_HC : Contains all the persons link to a module

I want to display a matrix (Rows and Columns are dynamics) of the progression of all the persons in a section.

This data is an example of the qualification table :

+-------+-----------------+-----------+
| ID    | Name            | IdSection | 
+-------+-----------------+-----------+
|     1 | Be better in SQL| 1         |  
|     2 | Understand PIVOT| 1         |  
|     3 | Understand SQL  | 1         |  
|     4 | Be an expert SQL| 1         |   
|     5 | blabla...       | 2         |  
|     6 | blabla...       | 2         |  
|     7 | blabla...       | 2         |   
|     8 | blabla...       | 2         |    
|     9 | blabla...       | 2         |  
|     10| blabla...       | 3         |  
|     11| blabla...       | 3         |   
|     12| blabla...       | 3         |   

The module tables contains the link between multiples module for one qualification :

+-------+-----------+-----------+-------+
| ID    | Name      | IdQualif  |Mandatory| 
+-------+-----------+-----------+-------+
|     1 | Learn SUM | 1         |  1    | 
|     2 | Learn AVG | 1         |  1    | 
|     3 |Learn PIVOT| 1         |   1   | 
|     4 |Learn COUNT| 1         |   0   | 
|     5 | Blabla    | 2         |  1    | 
|     6 | Blabla    | 2         |  1    | 
|     7 | Blabla    | 2         |   1   | 
|     8 | Blabla    | 2         |   0   | 
|     9 | Blabla    | 3         |  1    |
|     10| Blabla    | 3         |  0    | 
|     11| Blabla    | 3         |   1   | 
|     12| Blabla    | 3         |   0   | 

The third table contains the link between people and module :

+-------+-----------+-----------+----------+
| ID    |IdPerson   | IdModule  |Date      | 
+-------+-----------+-----------+----------+
|     1 | 1         | 1         |08/11/2018| 
|     2 | 2         | 1         |10/12/2018| 
|     3 | 3         | 1         |08/01/2018| 
|     4 | 4         | 1         |05/02/2018|
|     5 | 1         | 2         |06/03/2018|
|     6 | 2         | 2         |07/04/2018|
|     7 | 5         | 2         |08/05/2018|
|     8 | 4         | 2         |09/06/2018|
|     9 | 4         | 3         |01/07/2018|
|     10| 2         | 3         |05/08/2018|
|     11| 3         | 3         |02/09/2018|
|     12| 4         | 3         |03/10/2018|

For example I select the section number 1, the columns has to be :

  • First column : IdPerson
  • Other Column : Id Qualification

the row has to be :

  • First cell : IdPerson

  • Other cell : Number of module mandatory validate by user (Date inferior CurrentDate) / Number of module mandatory available

+--------+-------+-----+-----+----+
|IdPerson| [1]   | [2] | [3] | [4]|
+--------+-------+---- +-----+----+
|     1  | 0.50  |0.20 |0    |  0 |
|     2  | 1     | 0   |0.32 |   1|
|     3  | 0.20  | 0   |     | 0.2|
|     4  | 1     | 0.5 |0.25 |0.58|
|     5  | 1     | 0.2 |0.52 | 0  |

All the data are wrong, it's just an example of tables similar to mine. With pivot i can generate dynamic row and column but i don't understand how i insert calculated value in cell.


SELECT  IdHeadcount,[28],[29],[30],[31],[32],[33],[34],[35],[45],[49] from 
            (
                select mh.idx_headcount AS IdHeadcount,m.idx_qualification AS IdQualification
                from tbl_106_module_headcount mh
                LEFT JOIN tbl_106_module m ON mh.idx_module=m.id_module

           ) x
            pivot 
            (
              COUNT(IdQualification)
                for IdQualification in ([28],[29],[30],[31],[32],[33],[34],[35],[45],[49]) 
            ) p 

In this example i can display qualification and id headcount but i don't know how I pass and display calculated value ... PLEASE HELP ME :'(

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.