CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user2976421
user2976421

Reputation: 173

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?

Upvotes: 17

Views: 282444

Answers (4)

Diaa
Diaa

Reputation: 1

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;

Upvotes: 0

msi77
msi77

Reputation: 1632

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

Upvotes: 5

M.Ali
M.Ali

Reputation: 69504

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

Upvotes: 26

LINQ2Vodka
LINQ2Vodka

Reputation: 3036

  1. Create function that parses incoming string (say "AABBCC") as a table of strings (in particular "AA", "BB", "CC").
  2. 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.

Upvotes: 2

Related Questions