Re-format column to include an ID.

[newtosql]

Hiya, I have a table listing 2000 item_names such as 'coastal', 'mountain', 'rescue' and so on. Each of these items has a task_id which represents the amount of time that may elapse before the 'item_instructions' for each item should be completed. I need to reformat the task_id column so that the task_id of each item has a incrementing prefix (shown in the far right column). The incrementing id re-starts at 01/... for each new item in the table. Note: The ItemID column is not a auto-generated or sequential number. This information is being converted from an excel spreadsheet to import in a sql table. Can you help? Can this be done?


ItemID Item_Names Item_instructions Task_Id (New task_id)
360000 Rescue Rescue info … 01:00 01/01:00
360001 Rescue Rescue info … 00:07 02/00:07
360002 Rescue Rescue info … 00:10 03/00:10
360003 Rescue Rescue info … 00:12 04/00:12
398001 Coastal Coastal info … 00:12 01/00:12
398002 Coastal Coastal info … 00:17 02/00:17
398003 Coastal Coastal info … 00:20 03/00:20
398004 Coastal Coastal info … 00:25 04/00:25
360004 Mountain Mountain info… 00:16 01/00:16
360005 Mountain Mountain info… 00:20 02/00:20
360006 Mountain Mountain info… 00:25 03/00:25
360007 Mountain Mountain info… 00:27 04/00:27
360008 Mountain Mountain info… 00:30 05/00:30

 

[ClaireHCM]

Cursor again.I know cursor is not efficient.But so far this is easiest in my mind.

declare @iid int
declare @name varchar(20)
declare @ins varchar(30)
declare @tid varchar(20)
declare @string varchar(500)
declare @id int
declare @newtid varchar(20)

declare cursor1 cursor for select ItemID,Item_names,Item_instructions,Task_Id from table1
open cursor1
fetch next from cursor1 into @iid,@name,@ins,@tid
set @id = 1
while @@fetch_status = 0
begin
set @newtid = replicate('0',2-len(@id))+convert(varchar,@id)+"/"+@tid

set @string = "insert into table2 select "+convert(varchar,@iid)+",'"+@name+"','"+@ins
+"','"+@newtid+"'"
exec (@string)


if @id =(select count(*) from table1 where item_names = @name and item_instructions = @ins)
begin
set @id = 1
end
else
begin
set @id = @id +1
end
fetch next from cursor1 into @iid,@name,@ins,@tid
end

close cursor1
deallocate cursor1

:-{} claire

 

[SQLSister]

I do have a concern that your ID column is not going to necessarily be unique. IF this is what you are using to link to other tables, it could be a problem.

 

[swampBoogie]

select ItemID, Item_Names,Item_instructions , Task_Id ,
(select right('0'+cast(count(*) as varchar(2)),2) + '/' + task_id from listingTable
where item_names = lt.item_names
and itemID <= lt.itemId ) as newTaskId
from listingTable as lt