CODEWITHSUNDEEP
sqlsql-servert-sqlloopscursor
blankon91
blankon91

Reputation: 515

How to create loop based on value of row?

I have problem when I use my query bellow to have a looping inside the cursor.

data in table1 will be like this:

id  |  data
----|---------
A   |  4
B   |  2
C   |  5

the result in table2 should be like this:

id  |  data
----|---------
A   |  1
A   |  1
A   |  1
A   |  1
B   |  1
B   |  1
C   |  1
C   |  1
C   |  1
C   |  1
C   |  1

I have SQL query with cursor like this:

DECLARE @table2 table ( id VARCHAR(500), data INTEGER)

DECLARE Cur CURSOR FOR
SELECT id, data FROM table1

OPEN Cur 

WHILE ( @@FETCH_STATUS = 0 )
    BEGIN
        DECLARE @LoopNum INTEGER
        DECLARE @tempID VARCHAR(255)
        DECLARE @tempDATA INTEGER

        FETCH NEXT FROM Cur INTO @tempID, @tempDATA

        set @LoopNum = 0

        WHILE @LoopNum < @tempDATA
            BEGIN
            INSERT INTO table2 (id, data)
            VALUES( @tempID, 1)
            SET @LoopNum = @LoopNum + 1
        END
    END

CLOSE Cur 
DEALLOCATE Cur 

SELECT * FROM table2

but the query didn't work. is there something wrong with my query? Thank you.

Upvotes: 1

Views: 2707

Answers (4)

Saravana Kumar
Saravana Kumar

Reputation: 3729

Use this query to the expected result.

CREATE TABLE #test
  (id   CHAR(1),data INT)

INSERT #test VALUES ('A',4)
INSERT #test VALUES('B',2)
INSERT #test VALUES('C',5);


SELECT s.id, 1 AS data
FROM #test s
INNER JOIN 
master.dbo.spt_values t ON t.type='P'
   AND t.number BETWEEN 1 AND s.data

Note: Refer this Why (and how) to split column using master..spt_values?

Upvotes: 4

Dudi Konfino
Dudi Konfino

Reputation: 1136

i used two loops 1. for each row 2. for number for duplicate insert

SET NOCOUNT on;
DECLARE @t table(row int IDENTITY(1,1),id varchar(10),data int)
INSERT INTO @t
SELECT * from xyz

DECLARE @x table(id varchar(10),data int)  --table to hold the new data
DECLARE @i int=(SELECT count (*) from xyz)  --number of rows main table
DECLARE @y int   --number of duplicate
DECLARE @p int=1  --number of rows
WHILE @i!=0  --loop until last row of main table
BEGIN 
SET @y=(SELECT data FROM @t WHERE row=@p)  --set @y for number of 'row duplicate'
WHILE @y!=0
BEGIN 
INSERT INTO @x 
SELECT id,1
FROM @t 
WHERE row=@p
SET @y=@y-1
END 
SET @p=@p+1
SET @i=@i-1
END 
SELECT * FROM @x

enter image description here

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Use a Recursive CTE which will help you to loop through the records.

CREATE TABLE #test
  (id   CHAR(1),data INT)

INSERT #test
VALUES ('A',4),('B',2),('C',5);

WITH cte
     AS (SELECT 1 AS da,id,data
         FROM   #test a
         UNION ALL
         SELECT da + 1,id,data
         FROM   cte a
         WHERE  da < (SELECT data
                      FROM   #test b
                      WHERE  a.id = b.id))
SELECT id,
       1 AS data
FROM   cte
ORDER  BY id

Upvotes: 0

bruno
bruno

Reputation: 2882

You actually don't need a loop

IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL 
   DROP TABLE #TEMP


SELECT 'A' AS ID, 4 AS DATA
INTO #TEMP UNION
SELECT 'B', 2 UNION
SELECT 'C', 5

;WITH CTE AS 
(
     SELECT 1 AS NUMBER
     UNION ALL
     SELECT NUMBER + 1
     FROM CTE
     WHERE NUMBER < 100
)
SELECT T.ID, 1
FROM   CTE C
INNER JOIN #TEMP T
ON C.NUMBER <= T.DATA
ORDER BY T.ID

Carefull that if you want ot generate a large set of numbers in the CTE it may become slower.

Upvotes: 1

Related Questions