SQL Separate Row [duplicate]

问题:

This question already has an answer here:

• SQL query to split column data into rows 3 answers

I have table ABC containing data as follows:

ID  Data
1   text|text|text
2   text|text|text

What is the query so I get the following result?

ID  Data
1   text
1   text
1   text
2   text
2   text
2   text

Could this Code be the correct way? How to use it so I get the IDs for the Data records?

回答1:

Upto SQL Server 2008 R2 I think, we don't have better option than this but in SQL Server 2012 with some extra added function it might be possible.

DECLARE @tblTemp TABLE
(
ID INT
, TextValue NVARCHAR(500)
)

INSERT INTO @tblTemp
SELECT 1,   'text|text|text'
UNION
SELECT 2,   'text|text|text'

SELECT * FROM @tblTemp

DECLARE 
@ID INT,
@Data NVARCHAR(MAX),
@Expression NVARCHAR(5);

DECLARE @Temp TABLE 
(
ID INT,
Data NVARCHAR(100)
) 


DECLARE TempCursor CURSOR FORWARD_ONLY STATIC FOR
SELECT ID, TextValue, '|' FROM @tblTemp 

-- Open cursor and try to fetch first element
OPEN TempCursor
FETCH NEXT FROM TempCursor INTO @ID, @Data, @Expression

-- loop while fetch returned next item
WHILE @@FETCH_STATUS = 0
BEGIN

    -- Do the processing
    DECLARE @Index INT
    SET @Index = 1

    WHILE (CHARINDEX(@Expression, @Data) > 0)
    BEGIN
        INSERT INTO @Temp(ID, data)
        SELECT @ID, Data = LTRIM(RTRIM(SUBSTRING(@Data, 1, CHARINDEX(@Expression, @Data)-1)))

        SET @Data = SUBSTRING(@Data, CHARINDEX(@Expression, @Data) + 1, LEN(@Data))
        SET @Index = @Index + 1
    END

    INSERT INTO @Temp (ID, Data)
    SELECT @ID, Data = LTRIM(RTRIM(@Data))

    -- fetch next entry
    FETCH NEXT FROM TempCursor INTO @ID, @Data, @Expression
END

-- Close and deallocate cursor
CLOSE TempCursor
DEALLOCATE TempCursor

SELECT DISTINCT * FROM @Temp

回答2:

Dear friend create sql function and then call it

CreateFUNCTION [dbo].[SplitTest](@String varchar(MAX), @Delimiter char(1))    
as 
begin 
declare @idx int       
declare @slice varchar(8000)       

select @idx = 1       
    if len(@String)<1 or @String is null  return       

while @idx!= 0       
begin       
    set @idx = charindex(@Delimiter,@String)       
    if @idx!=0       
        set @slice = left(@String,@idx - 1)       
    else       
        set @slice = @String       

    if(len(@slice)>0)  
        insert into @temptable(Items) values(@slice)       

    set @String = right(@String,len(@String) - @idx)       
    if len(@String) = 0 break       
end   
return  
end;

Select ID, SplitTest(Data,'|') From YourTable