For SQL Gurus ... Problem With Matrix

Are many days that I've a problem with a matrix.... I hope that somebody can help me :-((

CREATE TABLE [dbo].[Matrice](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IDProfilo] [int] NULL,
    [IDAttivita] [int] NULL,
    [CodiceCompetenza] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
    [ObiettivoAttivita] [int] NULL,
    [PesoCompetenza] [int] NULL,
 CONSTRAINT [PK_Matrice] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

in this table I've a matrix with ... for each profile ... IDAttivita e CodiceCompetenza


in the row I've a list of activites (3) and for each activity I've an "ObiettivoAttivia"
in the columns I've all "CodiceCompetenza"

In excel I have a table like this

Attività - Obiettivo attività - SM - CO - CM - CT
att 1 - 4 - ...
att 2 - 3 - ...
att 3 - 2 - ...

"Obiettivo attività " is the same for all SM - CO - CM - CT of the same "Attività "
In my SQL table I've

[IDAttivita] row
[CodiceCompetenza] column
[ObiettivoAttivita] one for each activity
[PesoCompetenza] row/column value


this is an example:



in my external table I've
Profile
116 IDProfilo

Activities
1 att 1
2 att 2
3 att 3

Competenze
SM
CO
CM
CT



INSERT INTO Matrice VALUES (116,1,'SM',4,1)
INSERT INTO Matrice VALUES (116,1,'CO',4,2)
INSERT INTO Matrice VALUES (116,1,'CM',4,3)
INSERT INTO Matrice VALUES (116,1,'CT',4,1)

INSERT INTO Matrice VALUES (116,2,'SM',3,1)
INSERT INTO Matrice VALUES (116,2,'CO',3,1)
INSERT INTO Matrice VALUES (116,2,'CM',3,2)
INSERT INTO Matrice VALUES (116,2,'CT',3,2)

INSERT INTO Matrice VALUES (116,3,'SM',2,1)
INSERT INTO Matrice VALUES (116,3,'CO',2,3)
INSERT INTO Matrice VALUES (116,3,'CM',2,4)
INSERT INTO Matrice VALUES (116,3,'CT',2,1)

with these inserts I see that:

attività 1 has "obiettivo attivita" 4
attività 2 has "obiettivo attivita" 3
attività 3 has "obiettivo attivita" 2
my problem:

I calculate the "valore peso" ... the product (obiettivo attivita * PesoCompetenza) for each row


Now I've to to sum all "valore peso" of the same "CodiceCompetenza"

I will have

for SM
4*1=1
3*1=3
2*1=2
Total SM 9

for CO
4*2=8
3*1=3
2*3=6
Total CO 17

...
Total CM 26
Total CT 12

now I've to se a variable @range_massimo = 4


and calculate the  "Max" variable as a sum of all "obiettivi attività " * @range_massimo
(I've to consider only the 3 different activities for the "obiettivo attivita" and not all 12 activities of the matrix)
"Max" will be:
(4*4)+(3*4)+(2*4) = 36 -> "Max"

The final step is Total / Max

(for each Total of my  Competenza)
Total SM
Total CO
Total CM
Total CT

...

(9/36)*100 = 25
(17/36)*100 = 47,2
(26/36)*100 = 72,2
(12/36)*100 = 33,3


now
 
* @range_massimo /100

(25*4)/100= 1,0
(47,2*4)/100=1,9
(72,2*4)/100=2,9
(33,3*4)/100=1,3


I hope you can help me!!

Please note that I will have dinamic IDAttivita and CodiceCompetenza for each IDProfilo!

Thanks