[sql] In SQL Server, how to create while loop in select

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?

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;

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

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