drop table #temp
Create Table #Temp
(
col1 Varchar(20),
col2 Varchar(20),
Col3 Varchar(50),
col4 Varchar(20)
)
Select * From #Temp
Insert Into #Temp(col1)
Select * From SplitDelimiterString('123,456', ',')
Insert Into #Temp(col2)
Select * From SplitDelimiterString('abc,def', ',')
Insert Into #Temp(Col3)
Select * From SplitDelimiterString('fff,ggg', ',')
Insert Into #Temp(col4)
Select * From SplitDelimiterString('520002,520003', ',')
Select * From #Temp
За информация, SplitDelimiterString е функция. -- Код за SplitDelimiterString
Create FUNCTION [dbo].[SplitDelimiterString] (@StringWithDelimiter VARCHAR(8000), @Delimiter VARCHAR(8)) RETURNS @ItemTable TABLE (Item VARCHAR(8000)) AS BEGIN DECLARE @StartingPosition INT; DECLARE @ItemInString VARCHAR(8000); SELECT @StartingPosition = 1; --Return if string is null or empty IF LEN(@StringWithDelimiter) = 0 OR @StringWithDelimiter IS NULL RETURN; WHILE @StartingPosition > 0 BEGIN --Get starting index of delimiter .. If string --doesn't contain any delimiter than it will returl 0 SET @StartingPosition = CHARINDEX(@Delimiter,@StringWithDelimiter); --Get item from string IF @StartingPosition > 0 SET @ItemInString = SUBSTRING(@StringWithDelimiter,0,@StartingPosition) ELSE SET @ItemInString = @StringWithDelimiter; --If item isn't empty than add to return table IF( LEN(@ItemInString) > 0) INSERT INTO @ItemTable(Item) VALUES (@ItemInString); --Remove inserted item from string SET @StringWithDelimiter = SUBSTRING(@StringWithDelimiter,@StartingPosition + LEN(@Delimiter),LEN(@StringWithDelimiter) - @StartingPosition) --Break loop if string is empty IF LEN(@StringWithDelimiter) = 0 BREAK; END RETURN END
-- Наборът от резултати е
Col1 Col2 Col3 Col4 123 NULL NULL NULL 456 NULL NULL NULL NULL abc NULL NULL NULL def NULL NULL NULL NULL fff NULL NULL NULL ggg NULL NULL NULL NULL 520002 NULL NULL NULL 520003
-- Имам нужда от набор от резултати като
-- Наборът от резултати е
col1 col2 col3 col4 123 abc fff 520002 456 def ggg 520003
Моля помогнете.