CODEWITHSUNDEEP
sql-server
Shekhar Dalvi
Shekhar Dalvi

Reputation: 209

Summary of Service rating in SQL

I have a ServiceRating table like given below

enter image description here
Basically, the above table record the rate given to specific vendor by differnet customer across 5 different questions. Rate is given out of 5. I need to show summarize output of the above like given below

enter image description here

I have achive this result using below query.

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

Question : Is there any alternative way(using pivot) or better than the above to get the desire result

Upvotes: 3

Views: 90

Answers (1)

Jayvee
Jayvee

Reputation: 10875

you may need unpivot by question and then pivot again by rate, something like this:

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

Upvotes: 1

Related Questions