Id RatingStar Count Companyid
+----------------------------+ | ID RatingStar Companyid | +----------------------------+ | 1 5 5636 | | 2 5 5636 | | 3 1 101 | | 4 2 959 | +----------------------------+
+-------------------------------------------------------------------------------------------+ | 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 | +-------------------------------------------------------------------------------------------+
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))
CompanyID 1 2 3 4 5 5636 0 0 0 0 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
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)