In SQL Server, how to create while loop in select

Question

data will be like this:

    id  |  data
    ----|---------
    1   |  AABBCC
    2   |  FFDD
    3   |  TTHHJJKKLL

what the result I want is

    id  |  data
    ----|---------
    1   |  AA
    1   |  BB
    1   |  CC
    2   |  FF
    2   |  DD
    3   |  TT
    3   |  HH
    3   |  JJ
    3   |  KK
    3   |  LL

I have a sql code using cursor

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

DECLARE Cur CURSOR FOR
SELECT id FROM table1

OPEN Cur 

WHILE ( @@FETCH_STATUS = 0 )
    BEGIN
        DECLARE @LoopNum INTEGER
        DECLARE @tempID INTEGER
        DECLARE @tempDATA VARCHAR(255)
        FETCH NEXT FROM Cur INTO @tempID
        SET @tempDATA = SELECT data from table1 where id = @teampID
        SET @LoopNUM = 0
        WHILE @LoopNum< len(@tempDATA) / 2
            BEGIN
            INSERT INTO table2 (id, data)
            VALUES( @tempID, SUBSTRING(@tempDATA, @LoopNum * 2 +1, 2))
            SET @LoopNum = @LoopNum + 1
        END
    END

CLOSE Cur 
DEALLOCATE Cur 

SELECT * FROM table2

I don't want to use CURSOR because it's too slow, I have many data. Does anyone has good idea for it? Is it possible?

M.Ali · Accepted Answer · 2013-11-10 16:41:24Z

You Could do something like this .....
Your Table

CREATE TABLE TestTable 
(
ID INT,
Data NVARCHAR(50)
)
GO

INSERT INTO TestTable
VALUES (1,'AABBCC'),
       (2,'FFDD'),
       (3,'TTHHJJKKLL')
GO

SELECT * FROM TestTable

My Suggestion

CREATE TABLE #DestinationTable
(
ID INT,
Data NVARCHAR(50)
)
GO  
    SELECT * INTO #Temp FROM TestTable

    DECLARE @String NVARCHAR(2)
    DECLARE @Data NVARCHAR(50)
    DECLARE @ID INT

    WHILE EXISTS (SELECT * FROM #Temp)
     BEGIN 
        SELECT TOP 1 @Data =  DATA, @ID = ID FROM  #Temp

          WHILE LEN(@Data) > 0
            BEGIN
                SET @String = LEFT(@Data, 2)

                INSERT INTO #DestinationTable (ID, Data)
                VALUES (@ID, @String)

                SET @Data = RIGHT(@Data, LEN(@Data) -2)
            END
        DELETE FROM #Temp WHERE ID = @ID
     END


SELECT * FROM #DestinationTable

Result Set

ID  Data
1   AA
1   BB
1   CC
2   FF
2   DD
3   TT
3   HH
3   JJ
3   KK
3   LL

DROP Temp Tables

DROP TABLE #Temp
DROP TABLE #DestinationTable

msi77 · Accepted Answer · 2013-11-11 14:59:47Z

5

No functions, no cursors. Try this

with cte as(
select CHAR(65) chr, 65 i 
union all
select CHAR(i+1) chr, i=i+1 from cte
where CHAR(i) <'Z'
)
select * from(
SELECT id, Case when LEN(data)>len(REPLACE(data, chr,'')) then chr+chr end data 
FROM table1, cte) x
where Data is not null

answered Nov 11, 2013 at 14:59

msi77

1,6321 gold badge11 silver badges10 bronze badges

2 Comments

Tony Over a year ago

I wanted to test this out so I built a SQLFiddle

LINQ2Vodka Over a year ago

+1 Didnt test this, but CTE is a good option too for this task.

LINQ2Vodka · Accepted Answer · 2013-11-10 16:39:24Z

2

Create function that parses incoming string (say "AABBCC") as a table of strings (in particular "AA", "BB", "CC").
Select IDs from your table and use CROSS APPLY the function with data as argument so you'll have as many rows as values contained in the current row's data. No need of cursors or stored procs.

edited Nov 10, 2013 at 16:39

answered Nov 10, 2013 at 15:47

LINQ2Vodka

3,0362 gold badges31 silver badges49 bronze badges

Comments

Blue · Accepted Answer · 2016-08-11 10:35:30Z

0

INSERT INTO Table2 SELECT DISTINCT ID,Data = STUFF((SELECT ', ' + AA.Data FROM Table1 AS AA WHERE AA.ID = BB.ID FOR XML PATH(''), TYPE).value('.','nvarchar(max)'), 1, 2, '') FROM Table1 AS BB 
GROUP BY ID,Data
ORDER BY ID;

edited Aug 11, 2016 at 10:35

Blue

23k7 gold badges69 silver badges102 bronze badges

answered Aug 11, 2016 at 10:30

Diaa

1

Collectives™ on Stack Overflow

In SQL Server, how to create while loop in select

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related