How to create loop based on value of row?

Question

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.

Loop with an SQL-Statement or by programcode? – Edi G. Nov 27 '14 at 08:25 — Edi G., Nov 27 '14 at 08:25
@EdiG. loop with an SQL-Statement – blankon91 Nov 27 '14 at 08:49 — blankon91, Nov 27 '14 at 08:49

score 4 · Accepted Answer · edited May 23 '17 at 12:27

4

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?

edited May 23 '17 at 12:27

Community

1
1

answered Nov 27 '14 at 09:46

Saravana Kumar

3,669
5
15
35

score 1 · Answer 2 · answered Nov 27 '14 at 08:38

1

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.

answered Nov 27 '14 at 08:38

bruno

2,802
1
23
23

it works, but I have a large set of rows and number each row. Is there any alternative instead of using CTE? – blankon91 Nov 27 '14 at 08:53
@blankon91, you can replace the CTE with Stacked CTEs. Tell me if you want me to edit the post with an example. – bruno Dec 02 '14 at 08:24

score 0 · Answer 3 · answered Nov 27 '14 at 08:38

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

score 0 · Answer 4 · answered Nov 27 '14 at 09:30

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

change 'xyz' to your table name.use it as an alternative to cte. — Dudi Konfino, Nov 27 '14 at 10:03

How to create loop based on value of row?

4 Answers4