如何在t-sql中透视和计算百分比？

I have a table that looks like this:

我有一个看起来像这样的表:

    DECLARE @myTable TABLE (country varchar(max), code int)  
    INSERT @myTable
    SELECT 'A', 1 UNION ALL
    SELECT 'A', 1 UNION ALL
    SELECT 'A', 1 UNION ALL
    SELECT 'A', 2 UNION ALL
    SELECT 'A', 2 UNION ALL
    SELECT 'B', 1 UNION ALL
    SELECT 'B', 1 UNION ALL
    SELECT 'B', 1 UNION ALL
    SELECT 'B', 1 UNION ALL
    SELECT 'B', 2 UNION ALL
    SELECT 'C', 1 UNION ALL
    SELECT 'C', 1 UNION ALL
    SELECT 'C', 1 ;

I want to pivot off the A/B/C and then count the number of 2s and have the percentage of the total the 2 is.

我想绕过A / B / C,然后计算2的数量,并得到2的总和的百分比。

I can get the number of 2s with this query

我可以通过此查询获得2的数量

    DECLARE @mySecondTable TABLE (country varchar(max), code int);
    INSERT @mySecondTable
       SELECT * FROM @myTable 
       WHERE code=2;

    SELECT [A], [B], [C]
    FROM
     (SELECT Country, code
      FROM @mySecondTable) AS source
    PIVOT
    (
      COUNT(code)
      FOR Country IN ([A], [B], [C]) ) AS pvt;

But I really want it to look like this:

但我真的希望它看起来像这样:

    A          B         C
  2 (40.0%)   1 (20.0%)  0 

How do I get the totals and calculate the percentages ?

如何获得总计并计算百分比?

Thanks!

1 个解决方案

#1

DECLARE @myTable TABLE (country varchar(max), code int)  
    INSERT @myTable
    SELECT 'A', 1 UNION ALL
    SELECT 'A', 1 UNION ALL
    SELECT 'A', 1 UNION ALL
    SELECT 'A', 2 UNION ALL
    SELECT 'A', 2 UNION ALL
    SELECT 'B', 1 UNION ALL
    SELECT 'B', 1 UNION ALL
    SELECT 'B', 1 UNION ALL
    SELECT 'B', 1 UNION ALL
    SELECT 'B', 2 UNION ALL
    SELECT 'C', 1 UNION ALL
    SELECT 'C', 1 UNION ALL
    SELECT 'C', 1 ;

    DECLARE @mySecondTable TABLE (country varchar(10), pct varchar(20), code int);
    INSERT @mySecondTable
       SELECT country
       , pct=cast(count(*)over(partition by country,code) as varchar(10))
            +' ('+cast(100*
                cast(count(*)over(partition by country,code)as decimal(3,2))
                / CAST(count(*)over(partition by country) as decimal(3,2)) as varchar(10))
            +'%)'
       , code
       FROM @myTable

    SELECT [A], [B], [C]
    FROM
     (SELECT Country, pct
      FROM @mySecondTable
      WHERE code=2
      ) AS source
    PIVOT
    (
      MAX(pct)
      FOR Country IN ([A], [B], [C]) ) AS pvt;

Result: