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 lt  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

User · Answer

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   lt  Z    select   from  SELECT id  Case when LEN data  gt len REPLACE data  chr      then chr chr end data  FROM table1  cte  x where Data is not null

User · Answer

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

User · Answer

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   gt  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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql