13,150,344 members (30,139 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 ?

Code block added - OriginalGriff[/edit]
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```

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

Top Experts
Last 24hrsThis month
 OriginalGriff 263 Richard MacCutchan 123 CPallini 85 Karthik Bangalore 65 Wendelius 60
 OriginalGriff 6,591 Graeme_Grant 5,111 ppolymorphe 2,114 CPallini 1,940 Jochen Arndt 1,904

Advertise | Privacy |
Web02 | 2.8.170924.2 | Last Updated 14 Feb 2013