12,395,358 members (64,194 online)
Rate this:
See more: , +
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 ?

Posted 12-Feb-13 23:56pm
Updated 12-Feb-13 23:58pm
v2

Rate this:

## Solution 1

Try below query:
```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
...```
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?
Rate this:

## Solution 2

```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```

Top Experts
Last 24hrsThis month
 OriginalGriff 225 Jochen Arndt 130 Richard Deeming 105 Richard MacCutchan 80 khaled Ezzat 70
 OriginalGriff 6,008 Karthik Bangalore 2,382 ppolymorphe 2,350 F-ES Sitecore 1,877 Richard MacCutchan 1,707