当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server 2005 - SQL select : splitting a field into multiple rows tokenized by char (13)

问题描述:

I have a query as :

SELECT 'Item' AS TypeID,

iORCompID AS iEntityID,

iORCompID AS iParentEntityID,

'' as Source,

vComments as Comment

FROM

OrderResultComponents

WHERE IOrderID = @IEntityID

The data i get is following

TypeID | iEntityID | iParentEntityID | Source | Comment

Item | 1045 | 1045 | | Item Found some pending comments \X000d\ by UserID1

Item | 1027 | 1027 | | Item Found with some pending comments \X000d\ by UserID2

Item | 5389 | 5389 | | Item Found with \X000d\ some \X000d\ pending comments \X000d\ by UserID1

(\X000d\ is Char(13) i guess)

However i want data to be like :

TypeID | iEntityID | iParentEntityID | Source | Comment

Item | 1045 | 1045 | | Item Found some pending comments

Item | 1045 | 1045 | | by UserID1

Item | 1027 | 1027 | | Item Found with some pending comments

Item | 1027 | 1027 | | by UserID2

Item | 5389 | 5389 | | Item Found with

Item | 5389 | 5389 | | some

Item | 5389 | 5389 | | pending comments

Item | 5389 | 5389 | | by UserID1

i.e. I want to split my Comment field with next line character of DB and repeat the other fields with this split...Any help???

EDIT:

Ah,

I got a hint from Split one column into multiple rows

Please correct my query if its not valid.

SELECT

'Item' AS TypeID, '' as SetID,T.iORCompID , RIGHT(LEFT(T.vComments,Number-1),

CHARINDEX(char(13),REVERSE(LEFT(char(13)+T.vComments,Number-1))))

FROM master..spt_values, OrderResultComponents T

WHERE Type = 'P'

AND Number BETWEEN 1

AND LEN(T.vComments)+1

AND (SUBSTRING(T.vComments,Number,1) = char(13) ) AND T.IOrderID = @iEntityID

网友答案:
declare @index int;
declare @TypeID varchar(10),@left varchar(max);
declare @iEntityID int,@iParentEntityID int;
declare @Source varchar(max),@Comment varchar(max);

declare split_cursor cursor for 
select * from OrderResultComponents
open split_cursor
fetch next from split_cursor into @TypeID,@iEntityID,@iParentEntityID,@Source,@Comment
while (@@fetch_status=0)
begin
    set @index=charindex('\X000d',@Comment);
    while(@index!=0)
        begin
            set @left=substring(@Comment,1,@index-1);
            set @Comment=substring(@Comment,@index+8,len(@Comment));
            insert into #split values (@TypeID,@iEntityID,@iParentEntityID,@Source,@left);
            set @index=charindex('\X000d',@Comment);
        end
        insert into #split values (@TypeID,@iEntityID,@iParentEntityID,@Source,@Comment);
    fetch next from split_cursor into @TypeID,@iEntityID,@iParentEntityID,@Source,@Comment
end
select * from OrderResultComponents;
select * from #split;
close split_cursor
deallocate split_cursor
truncate table #split
网友答案:

Without knowing any of the specifics about your application you will need to do something like this:

  • Query the database with your initial query and store it in a DataTable
  • Loop through the rows in the DataTable and concatenate your data with a StringBuilder
  • Write the contents of the StringBuilder to a text file

Check out this StackOverflow thread for information on how to create and write to a text file.

Please add a comment if you have any further questions.

分享给朋友:
您可能感兴趣的文章:
随机阅读: