daten werden so sein:
id | data
----|---------
1 | AABBCC
2 | FFDD
3 | TTHHJJKKLL
was ich will, ist das Ergebnis
id | data
----|---------
1 | AA
1 | BB
1 | CC
2 | FF
2 | DD
3 | TT
3 | HH
3 | JJ
3 | KK
3 | LL
Ich habe einen SQL-Code mit dem 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
Ich möchte CURSOR nicht verwenden, weil es zu langsam ist. Ich habe viele Daten. Hat jemand eine gute Idee dafür? Ist es möglich?
Sie könnten so etwas tun .....
Dein Tisch
CREATE TABLE TestTable
(
ID INT,
Data NVARCHAR(50)
)
GO
INSERT INTO TestTable
VALUES (1,'AABBCC'),
(2,'FFDD'),
(3,'TTHHJJKKLL')
GO
SELECT * FROM TestTable
Mein Vorschlag
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
Ergebnissatz
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
Keine Funktionen, keine Cursor. Versuche dies
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
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;