SQL Pivot table : grouping rows and adding % in columns-CodePudding

Good morning everybody, i'm starting to use pivot tables in SQL.

My starting table is very simple, just 2 variables : REGIONE and ESITO.

My first objective was to count ESITO for every REGIONE, so i did :

SELECT * FROM   
(
    SELECT 
        [ESITO],
        [REGIONE]
    FROM 
        [#temp]
) t
PIVOT(
    COUNT(ESITO) 
    FOR ESITO IN ([0],[9],[17],[19])
)

AS PivotTable

and it works fine, as i obtained :

https://i.stack.imgur.com/lvYdg.png

My next steps are : -Adding a column to obtain the % of the counts -Group the "REGIONE" variable to obtain 4 groups, each one has to show above the related cells.

So my result table should be smt like this :

https://i.stack.imgur.com/eAvIn.png

Could you help me to modify the query ? Thanks in advance Vittorio

CodePudding user response：

Looks like you just need to divide each value by the total across the row

SELECT
  REGIONE,
  [0],
  Pct0  = [0]  * 1.0 / ([0]   [9]   [17]   [19]),
  [9],
  Pct9  = [9]  * 1.0 / ([0]   [9]   [17]   [19]),
  [17],
  Pct17 = [17] * 1.0 / ([0]   [9]   [17]   [19]),
  [19],
  Pct19 = [19] * 1.0 / ([0]   [9]   [17]   [19])
FROM
(
    SELECT 
        ESITO,
        REGIONE
    FROM 
        #temp
) t
PIVOT(
    COUNT(ESITO) 
    FOR ESITO IN ([0],[9],[17],[19])
) pvt;

In future a Minimal Reproducible Example would help immensely.

CodePudding user response：

Thank you so much for you help jarlh and Charlieface.

I'll be more accurate on posting next time !

Have a nice day :)