This query gives me the desired result but i can't run this query every time.The 2 loops is costing me.So i need to implement something like view.But the logic has temp tables involved which isn't allowed in views as well.so, is there any other way to store this result or change the query so that it will cost me less.
DECLARE @Temp TABLE (
[SiteID] VARCHAR(100)
,[StructureID] INT
,[row] DECIMAL(4, 2)
,[col] DECIMAL(4, 2)
)
DECLARE @siteID VARCHAR(100)
,@structureID INT
,@struct_row INT
,@struct_col INT
,@rows_count INT
,@cols_count INT
,@row INT
,@col INT
DECLARE structure_cursor CURSOR
FOR
SELECT StructureID
,SiteID
,Cols / 8.5 AS Cols
,Rows / 11 AS Rows
FROM Structure
WHERE SellerID = 658 --AND StructureID = 55
OPEN structure_cursor
FETCH NEXT
FROM structure_cursor
INTO @structureID
,@siteID
,@struct_col
,@struct_row
SELECT @rows_count = 1
,@cols_count = 1
,@row = 1
,@col = 1
WHILE @@FETCH_STATUS = 0
BEGIN
WHILE @row <= @struct_row
BEGIN
WHILE @col <= @struct_col
BEGIN
--PRINT 'MEssage';
INSERT INTO @Temp (
SiteID
,StructureID
,row
,col
)
VALUES (
@siteID
,@structureID
,@rows_count
,@cols_count
)
SET @cols_count = @cols_count + 1;
SET @col = @col + 1;
END
SET @cols_count = 1;
SET @col = 1;
SET @rows_count = @rows_count + 1;
SET @row = @row + 1;
END
SET @row = 1;
SET @col = 1;
SET @rows_count = 1;
FETCH NEXT
FROM structure_cursor
INTO @structureID
,@siteID
,@struct_col
,@struct_row
END
CLOSE structure_cursor;
DEALLOCATE structure_cursor;
SELECT * FROM @Temp
You can generate the number of rows and columns and then CROSS APPLY with those, like below. I've left out your SellerID condition.
;WITH Cols
AS
(
SELECT StructureID, SiteID, CAST(Cols / 8.5 AS INT) AS Col
FROM Structure
UNION ALL
SELECT s.StructureID, s.SiteID, Col - 1
FROM Structure s
INNER JOIN Cols c ON s.StructureID = c.StructureID AND s.SiteID = c.SiteID
WHERE Col > 1
)
, Rows
AS
(
SELECT StructureID, SiteID, CAST(Rows / 11 AS INT) AS Row
FROM Structure
UNION ALL
SELECT s.StructureID, s.SiteID, Row - 1
FROM Structure s
INNER JOIN Rows r ON s.StructureID = r.StructureID AND s.SiteID = r.SiteID
WHERE Row > 1
)
--INSERT INTO @Temp (SiteID, StructureID, row, col)
SELECT s.SiteID, s.StructureID, r.Row, c.Col
FROM Structure s
CROSS APPLY Cols c
CROSS APPLY Rows r
WHERE s.StructureID = c.StructureID AND s.SiteID = c.SiteID
AND s.StructureID = r.StructureID AND s.SiteID = r.SiteID