Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Posts
    6

    Angry Unanswered: tricky...may be impossible!!!

    Hi,
    I've a table where every record looks like this:

    ID: 2154
    Info: 12;54;56;12;54;2;8;45;98;5;6;74;38;.......
    Date: 19/02/02

    I would like, using a store procedure, to store in a better way these info, in a 2nd table like this:

    ID: 2154
    Info1: 12
    Info2: 54
    Info3: 56
    Info4: 12
    ...
    Date: 19/02/02

    In other words, I want to split the string "Info" in different fields ("Info1","Info2",...), one for each semicolon-separated number.

    Do you think it would be possible in a store procedure?
    Any idea?

    Thanks,
    Stefano

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    yuk
    why not have the table
    id, info, date, seq

    something like this - I'll leave it to you to debug.
    The table structure you have given can be done in a similar way using dynamic sql and @seq as a variable rather than on #a.

    @sql = 'update newtbl set info' + convert(varchar(3),@seq) = left(info,i) from #a where i <> 0'
    exec (@sql)

    This is the one for the structure above - for your structure replace the insert with the statement above - use insert distinct id, date to add all the rows.

    create table #a(id int, info varchar(1000), date datetime, i int, seq int)
    insert #a
    select id, info, date, 1,0
    from tbl

    while @@rowcount >0
    begin
    update #a
    set i = case when charindex(info,';') = 0 then len(info)+1 else charindex(info,';') end,
    seq = seq + 1
    where i <> 0

    insert newtbl
    select id, left(info,i-1), date, seq from #a
    where i <> 0

    update #a
    set info = "", i = 0
    where len(info) = i
    and i <> 0

    update #a
    set info = right(info,len(info)-i)
    where i <> 0

    end

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I've tried to answer this question so that it fits your needs, even though it is not the best design. Two items, (1) The answer has a fix size of 15 info columns, info1...info2. You can increase this to your system requirements. (2) The info data (21;4;456 end in a ';', if not you can tack one on in the SELECt statement for the cursor.

    Code:
     
    set nocount on
    go
    drop table tblinfo
    drop table Newinfo
    go
    -- Create Test environment
    create table tblinfo
    	(
    	infoID		int,
    	info		varchar(100),
    	infoDate	datetime
    	)
    GO
    insert tblinfo values (2358,'12;54;6;7898;15;76;9;458;899;25;3;55;','19/02/02')
    insert tblinfo values (2118,'1;','19/01/02')
    insert tblinfo values (1857,'132;45;78979;22;1;0;','30/01/02')
    insert tblinfo values (1995,'132;45;78979;22;1;0;12;54;6;7898;15;76;9;458;899;','22/02/02')
    insert tblinfo values (186,'132;45;78979;22;1;0;12;54;6;7898;15;76;449;458;9;458;899;','07/02/02')
    GO
    create table NewInfo
    	(
    	infoID		int,
    	infoDate	datetime,
    	info1		int,
    	info2		int,
    	info3		int,
    	info4		int,
    	info5		int,
    	info6		int,
    	info7		int,
    	info8		int,
    	info9		int,
    	info10		int,
    	info11		int,
    	info12		int,
    	info13		int,
    	info14		int,
    	info15		int
    	)
    GO
    
    declare	@info	varchar(100),
            @dt	datetime,
    	@i	int,
            @x	int,
    	@c1	int,
    	@c2	int,
    	@c3	int,
    	@c4	int,
    	@c5	int,
    	@c6	int,
    	@c7	int,
    	@c8	int,
    	@c9	int,
    	@c10	int,
    	@c11	int,
    	@c12	int,
    	@c13	int,
    	@c14	int,
    	@c15	int,
    	@id	int
    
    declare info_cur cursor
    for
    select 	infoID, info,infoDate
    from	tblinfo
    
    open info_cur
    
    fetch info_cur into @id, @info, @dt
    
    while (@@fetch_status = 0) begin
        select @c1	= 0,
    	   @c2	= 0,
    	   @c3	= 0,
    	   @c4	= 0,
    	   @c5	= 0,
    	   @c6	= 0,
    	   @c7	= 0,
    	   @c8	= 0,
    	   @c9	= 0,
    	   @c10	= 0,
    	   @c11	= 0,
    	   @c12	= 0,
    	   @c13	= 0,
    	   @c14	= 0,
    	   @c15	= 0,
               @i   = 1
    
        WHILE (DATALENGTH(@info) > 1) AND (@i < 16) BEGIN
            SELECT  @x = CONVERT(int,SUBSTRING(@info,1,CHARINDEX(";",@info)-1))
    
            SELECT  @info = RIGHT(@info,DATALENGTH(@info)-CHARINDEX(";",@info)),
            	@c1   = CASE WHEN @i = 1  THEN @x ELSE @c1  END,
        		@c2   = CASE WHEN @i = 2  THEN @x ELSE @c2  END,
        		@c3   = CASE WHEN @i = 3  THEN @x ELSE @c3  END,
        		@c4   = CASE WHEN @i = 4  THEN @x ELSE @c4  END,
        		@c5   = CASE WHEN @i = 5  THEN @x ELSE @c5  END,
        		@c6   = CASE WHEN @i = 6  THEN @x ELSE @c6  END,
        		@c7   = CASE WHEN @i = 7  THEN @x ELSE @c7  END,
        		@c8   = CASE WHEN @i = 8  THEN @x ELSE @c8  END,
        		@c9   = CASE WHEN @i = 9  THEN @x ELSE @c9  END,
        		@c10  = CASE WHEN @i = 10 THEN @x ELSE @c10 END,
        		@c11  = CASE WHEN @i = 11 THEN @x ELSE @c11 END,
        		@c12  = CASE WHEN @i = 12 THEN @x ELSE @c12 END,
        		@c13  = CASE WHEN @i = 13 THEN @x ELSE @c13 END,
        		@c14  = CASE WHEN @i = 14 THEN @x ELSE @c14 END,
        		@c15  = CASE WHEN @i = 15 THEN @x ELSE @c15 END,
    		@i    = @i + 1
        END
    
        insert NewInfo (infoID,infoDate,info1,info2,info3,info4,
                        info5,info6,info7,info8,info9,info10,
                        info11,info12,info13,info14,info15)
        values (@id,@dt,@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10,@c11,@c12,@c13,@c14,@c15)
    
        fetch info_cur into @id, @info, @dt
    END
    
    close info_cur
    deallocate info_cur
    go
    
    select * from tblinfo
    select * from Newinfo
    go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •