Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have two tables Company and CompanyLike

CompanyLike

Id
RatingStar
Count
Companyid

Company and CompanyLike have 1 to Many relation . One company can have many likes

Company Like Table Will Look Like this

+----------------------------+
| ID  RatingStar   Companyid |
+----------------------------+
| 1      5           5636    |
| 2      5           5636    |
| 3      1           101     |
| 4      2            959    |
+----------------------------+

Now i want to get count of rating star that how many times RatingStar is 5 , and how many time its 2 against every Companyid

I want my result like this

+-------------------------------------------------------------------------------------------+
| ID     Companyid    RatingStar1   RatingStar2   RatingStar3   RatingStar4   RatingStar5   |
+-------------------------------------------------------------------------------------------+
| 1       5636               0           0               0          0             2         |
| 1       101                0           0               0          0             0         |
| 1       959                1           1               0          0             0         |
+-------------------------------------------------------------------------------------------+


How to do this ?

[edit]Code block added - OriginalGriff[/edit]
Posted
Updated 12-Feb-13 23:58pm
v2

Try below query:
SQL
DECLARE @cols NVARCHAR(200)

--rating star
SET @cols = '[1],[2],[3],[4],[5]'

SELECT [CompanyID], @cols
FROM (SELECT * FROM Company) AS DT
PIVOT (COUNT([CompanyId]) FOR [RatingStar] IN (@cols))


Result:
CompanyID    1     2    3   4   5
5636    0    0     0    0    2
...
 
Share this answer
 
Comments
Ahsanashfaqattari.12 13-Feb-13 7:24am    
I already have a solution with PIVOT but i don't want to use PIVOT
select *
from (
select r.id as rate,
c.id as comp,
l.idComp as n
from compa c
cross join rates r
left join likes l
on l.rating = r.id
and c.id = l.idComp
) as src
PIVOT
(
count( n )
FOR rate IN ( [1], [2], [3], [4], [5] )
) as pvt ;
Maciej Los 13-Feb-13 7:44am    
So, what are you excpecting from me?

2 stars???

I wan't waste my time next time...
Ahsanashfaqattari.12 14-Feb-13 0:31am    
If u are working on stars then look again u got 5 now , and i mean to say that i don't want to use PIVOT , you can do anyway as u like but except PIVOT and that was my little request . Please don't mind
Maciej Los 14-Feb-13 2:58am    
I'm not doing it for "stars"... I was wondering why your opinion (via voting system) is so bad/poor. That's why i'm asking: why 2 stars? I need to know what i'm doing wrong to improve my answer, but i need to know the reasons of poor mark.

Remember, the answer is as good as a question. I don't see the (in your post) the question: How to achieve desired result without using pivot tables? You question is: I want my result like this... How to do this? without the reservation that "no pivot tables!"

Finally, can you tell me what do you want to do?

If you want to achieve desired result without pivot tables, i'm answering you: it's possible, but it's more complicated. Do you want to know another (more complicated) solution?
SQL
select RatingStar as RatingStar,com.id as Companyid  ,com.Name as
         CompanyName ,

 Count(RatingStar) as Count,MIN(c.ID) as Id
     from CompanyReviewLike as c
   inner join Company as com on com.id = c.Companyid
 Group By
 RatingStar ,com.Name , com.id
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900