sql-server - SQL中的服务评级总结

Question

我有一个如下所示的 ServiceRating 表

基本上，上表记录了不同客户在 5 个不同问题中给特定供应商的费率。评分为满分 5 分。我需要像下面给出的那样显示上面的总结输出

我使用以下查询获得了这个结果。

DECLARE @tempTable TABLE
    (
         Question varchar(200) 
        ,[Excelent] int
        ,[Very Good] int
        ,[Good] int
        ,[Average] int
        ,[Poor] int 
    )

---

Insert INTO @tempTable
SELECT 'Q1'
    ,(SELECT COUNT(Q1) FROM ServiceRating Where Q1 = 5 )
    ,(SELECT COUNT(Q1) FROM ServiceRating Where Q1 = 4 )
    ,(SELECT COUNT(Q1) FROM ServiceRating Where Q1 = 3 )
    ,(SELECT COUNT(Q1) FROM ServiceRating Where Q1 = 2 )
    ,(SELECT COUNT(Q1) FROM ServiceRating Where Q1 = 1 )

Insert INTO @tempTable
SELECT 'Q2'
    ,(SELECT COUNT(Q2) FROM ServiceRating Where Q2 = 5 )
    ,(SELECT COUNT(Q2) FROM ServiceRating Where Q2 = 4 )
    ,(SELECT COUNT(Q2) FROM ServiceRating Where Q2 = 3 )
    ,(SELECT COUNT(Q2) FROM ServiceRating Where Q2 = 2 )
    ,(SELECT COUNT(Q2) FROM ServiceRating Where Q2 = 1 )

Insert INTO @tempTable
SELECT 'Q3'
    ,(SELECT COUNT(Q3) FROM ServiceRating Where Q3 = 5 )
    ,(SELECT COUNT(Q3) FROM ServiceRating Where Q3 = 4 )
    ,(SELECT COUNT(Q3) FROM ServiceRating Where Q3 = 3 )
    ,(SELECT COUNT(Q3) FROM ServiceRating Where Q3 = 2 )
    ,(SELECT COUNT(Q3) FROM ServiceRating Where Q3 = 1 )    

Insert INTO @tempTable
SELECT 'Q4'
    ,(SELECT COUNT(Q4) FROM ServiceRating Where Q4 = 5 )
    ,(SELECT COUNT(Q4) FROM ServiceRating Where Q4 = 4 )
    ,(SELECT COUNT(Q4) FROM ServiceRating Where Q4 = 3 )
    ,(SELECT COUNT(Q4) FROM ServiceRating Where Q4 = 2 )
    ,(SELECT COUNT(Q4) FROM ServiceRating Where Q4 = 1 )                            

Insert INTO @tempTable
SELECT 'Q5'
    ,(SELECT COUNT(Q5) FROM ServiceRating Where Q5 = 5 )
    ,(SELECT COUNT(Q5) FROM ServiceRating Where Q5 = 4 )
    ,(SELECT COUNT(Q5) FROM ServiceRating Where Q5 = 3 )
    ,(SELECT COUNT(Q5) FROM ServiceRating Where Q5 = 2 )
    ,(SELECT COUNT(Q5) FROM ServiceRating Where Q5 = 1 )

---

SELECT * FROM @tempTable    

问题:是否有任何替代方法(使用 pivot)或比上述方法更好的方法来获得所需的结果

Answer 1

您可能需要按问题逆向旋转，然后再按速率旋转，如下所示:

select rates, [5] as 'Excellent' ,[4] as 'Very Good',
              [3] as 'Good',[2] as 'Average' ,[1] as 'Poor'
From(
SELECT *
FROM 
   (SELECT rateTo, q1,q2,q3,q4,q5
   FROM ServiceRating) p
UNPIVOT
   (rate FOR rates IN 
      (q1,q2,q3,q4,q5)
) as unpvt
) x 
pivot
( 
count(rate)
for rate in 
([5],[4],[3],[2],[1])
) as pvt