How to implement a lookup table for a range of two column values

Edit: Updated two tables to synchronize with each other. These are the only tables used. just put it, I need to take an account of all unique entries and display it in 2d format.

I have a table of optical lenses, a sample of which follows:

Spherical|Cylindrical --------------------- 0 | 0.5 0.25 | 0.75 0.25 | 0.5 0 | 0 0 | 0.25 0 | 0.5 0.25 | 0.75 0.25 | 0.5 0.5 | 0 0.75 | 0 0.75 | 0 0.5 | 0.25 0.5 | 0.75 0.75 | 0.25 0.5 | 0.75 0.75 | 0.75 0.75 | 0.5 0.75 | 0.5 

etc.

I want to display a bird's eye for lens counting in each combination in 2d format as follows:

 Spherical/Cylindrical|0|0.25|0.5|0.75|... upto 8 in steps of 0.25 ----------------------------------------- 0 |1| 1 | 2 | 1 | 0.25 |0| 0 | 2 | 2 | 0.5 |1| 2 | 0 | 2 | 0.75 |2| 1 | 2 | 1 | ... upto 30 in steps of 0.25 

How to implement this in C # .net with sql server 2008? what would be the best approach?

I have a few thoughts:

  • Generate a view at runtime using a special request and format it in 2d
  • Create a table type 2d (in the format above) and update the count every time the lens table is updated.

Please give me your thoughts and advise on this. Thanks!

+4
source share
2 answers

Here is an example request for how to make a presentation:

 --build table variable and sample data DECLARE @Optical table (Spherical numeric(4,2),Cylindrical numeric(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) --query to use as a basis for the view ;with AllSpherical AS --this recursive CTE builds the 121 rows for: 0.00 to 30.0 ( SELECT convert(numeric(4,2),0.0) AS Spherical UNION ALL SELECT convert(numeric(4,2),Spherical+0.25) FROM AllSpherical WHERE Spherical<=29.75 ) SELECT s.Spherical ,SUM(CASE WHEN o.Cylindrical=0.00 THEN 1 ELSE 0 END) AS c_000 ,SUM(CASE WHEN o.Cylindrical=0.25 THEN 1 ELSE 0 END) AS c_025 ,SUM(CASE WHEN o.Cylindrical=0.50 THEN 1 ELSE 0 END) AS c_050 ,SUM(CASE WHEN o.Cylindrical=0.75 THEN 1 ELSE 0 END) AS c_075 ,SUM(CASE WHEN o.Cylindrical=1.00 THEN 1 ELSE 0 END) AS c_100 ,SUM(CASE WHEN o.Cylindrical=1.25 THEN 1 ELSE 0 END) AS c_125 ,SUM(CASE WHEN o.Cylindrical=1.50 THEN 1 ELSE 0 END) AS c_150 ,SUM(CASE WHEN o.Cylindrical=1.75 THEN 1 ELSE 0 END) AS c_175 --... add a case for all columns FROM AllSpherical s LEFT OUTER JOIN @Optical o ON s.Spherical=o.Spherical GROUP BY s.Spherical OPTION (MAXRECURSION 120) 

exit:

 Spherical c_000 c_025 c_050 c_075 c_100 c_125 c_150 c_175 ---------- ----- ----- ----- ----- ----- ----- ----- ----- 0.00 1 0 1 0 0 0 0 0 0.25 0 0 0 1 0 0 0 0 0.50 0 0 0 0 0 0 0 0 0.75 0 0 0 0 0 0 0 0 1.00 0 0 0 0 0 0 0 0 1.25 0 0 2 0 0 0 0 0 1.50 0 0 0 0 0 0 0 0 1.75 0 0 0 0 0 0 0 0 2.00 0 0 0 0 0 0 0 0 2.25 0 0 0 0 0 0 0 0 ... (121 row(s) affected) 

you can build a traditional view using this query if you update the raw data much more than you read this view. this will be your option 1

if you plan on reading this view much more than updating the raw data, consider saving the view: Improving performance with SQL Server 2005 indexed views and Creating indexed views . This basically materializes the view and when you insert / update / delete the base table, the data stored in the view is updated in the same way as an automatic system level trigger to synchronize them. this will be your option 2, but the system will do all the β€œhard” work of keeping everyone in sync.

+2
source

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; 
+2
source

Source: https://habr.com/ru/post/1395294/


All Articles