Borrowing from a KM table variable, here is another way to do this that uses PIVOT and avoids 33 SUM(CASE...) expressions SUM(CASE...) .
DECLARE @Optical TABLE (Spherical DECIMAL(4,2), Cylindrical DECIMAL(4,2)); INSERT INTO @Optical VALUES ( 0, 0.5); INSERT INTO @Optical VALUES (0.25, 0.75); INSERT INTO @Optical VALUES (1.25, 0.5); INSERT INTO @Optical VALUES (1.25, 0.5); INSERT INTO @Optical VALUES ( 0, 0); ;WITH x AS ( SELECT TOP (33) [row] = (ROW_NUMBER() OVER (ORDER BY [object_id])-1)*0.25 FROM sys.objects ORDER BY [row] ), y AS ( SELECT Spherical = x.[row], o.Cylindrical FROM x LEFT OUTER JOIN @Optical AS o ON x.[row] = o.Spherical ) SELECT pvt.* FROM y PIVOT (COUNT(y.Cylindrical) FOR y.Cylindrical IN ( [0.00],[0.25],[0.50],[0.75],[1.00],[1.25],[1.50],[1.75],[2.00],[2.25],[2.50],[2.75], [3.00],[3.25],[3.50],[3.75],[4.00],[4.25],[4.50],[4.75],[5.00],[5.25],[5.50],[5.75], [6.00],[6.25],[6.50],[6.75],[7.00],[7.25],[7.50],[7.75],[8.00] )) AS pvt ORDER BY pvt.Spherical;
Now you probably think that I donβt want to enter all these values ββin the PIVOT section, but you can quickly generate them:
DECLARE @sql NVARCHAR(MAX)= N''; ;WITH x AS ( SELECT TOP (33) [row] = (ROW_NUMBER() OVER (ORDER BY [object_id])-1)*0.25 FROM sys.objects ORDER BY [row] ) SELECT @sql = @sql + ',[' + RTRIM(CONVERT(DECIMAL(4,2), [row])) + ']' FROM x; SET @sql = STUFF(@sql, 1, 1, ''); PRINT @sql;