透视表中括号中的SQL Server 2005百分比 [英] SQL Server 2005 percentage in parenthesis in pivot table

问题描述

这是来自此处的后续问题. 我有一些输出，能够添加总计列，但是现在我需要使用该列来添加百分比，如下所示:

This is a follow up question from here. I have some output and was able to add a total column but now I need to use the column to add percentage like this:

| LESSONID     RESPONSE COUNT-> | 0       | 1       | 2       | 3       | 4       | N |
---------------------------------------------------------------------------------------                         
|lesson1                        | 1 (20%) | 1 (20%) | 1 (20%) | 1 (20%) | 1 (20%) | 5 |
|lesson2                        | 1 (20%) | 1 (20%) | 1 (20%) | 2 (40%) | 0       | 5 |
|lesson3                        | 1 (20%) | 1 (20%) | 0       | 3 (60%) | 0       | 5 |
|lesson4                        | 0       | 1 (20%) | 4 (80%) | 0       | 0       | 5 |
|lesson5                        | 0       | 5 (100%)| 0       | 0       | 0       | 5 |

我在这里找到了一些帮助，但无法将所有内容整合在一起.

I have found some help here but can't get it all to come together.

这是我到目前为止所拥有的

*此外，我也在使用SQLFiddle中未表示的SQL Server 2005

*Also I am on SQL Server 2005 which is not represented in SQLFiddle

推荐答案

您可以像设置其他格式一样设置数据透视查询的结果格式.因此，您可以将百分比连接到其各自的来源. 这是一个SQL字段.

You can format the result of your pivot query as you would do with any other. So, you might concatenate percentage to its respective source. Here is a SQL FIDDLE.

SELECT RC.lessonid AS 'lessonid     response count->'
  , convert (varchar(20), isnull([0], 0))
  + isnull (' ('
-- As both numbers are integers don't forget to cast one of them into double
-- If you dislike * 100 format or want more precise result.
  + convert (varchar(20), [0] * 100 / RCN.N)
  + '%)', '') as [0]
  , convert (varchar(20), isnull([1], 0))
  + isnull (' ('
  + convert (varchar(20), [1] * 100 / RCN.N)
  + '%)', '') as [1]
  , convert (varchar(20), isnull([2], 0))
  + isnull (' ('
  + convert (varchar(20), [2] * 100 / RCN.N)
  + '%)', '') as [2]
  , convert (varchar(20), isnull([3], 0))
  + isnull (' ('
  + convert (varchar(20), [3] * 100 / RCN.N)
  + '%)', '') as [3]
  , convert (varchar(20), isnull([4], 0))
  + isnull (' ('
  + convert (varchar(20), [4] * 100 / RCN.N)
  + '%)', '') as [4]
  ,RCN.N
FROM (
    SELECT lessonid
        ,response
        ,count(response) AS respcnt
    FROM tblRChoices
    GROUP BY lessonid
        ,response
    ) TableResponseCount
PIVOT(SUM(respcnt) FOR response IN (
            [0]
            ,[1]
            ,[2]
            ,[3]
            ,[4]
            )) RC
JOIN (SELECT lessonid, count(lessonid) as N FROM tblRChoices GROUP BY lessonid) RCN 
ON RC.lessonid = RCN.lessonid

这篇关于透视表中括号中的SQL Server 2005百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！