November 8, 2008 at 2:37 am
I have a table TEMP_TOOL1 and its columns are DESCRIPTION , ITEM1 , AMOUNT AND ITEM2.
ITEM1 AMOUNT DESCRIPTION ITEM2
A 10 aaaaaaa -
A 20 bbbbbbb -
A 45 ccccccc -
A 5 aaaaaaa -
A 12 aaaaaaa -
A 52 ccccccc -
B 10 aaaaaaa -
B 20 bbbbbbb -
B 45 ccccccc -
B 35 aaaaaaa -
B 12 aaaaaaa -
B 52 ccccccc - [etc]
I want to distinct in description all 'A' and 'B' (select distinct description, item1,ITEM2 from TEmp_tool1 where item1 = 'A') . and want to give a new name and add it to ITEM2 coloumn.
ITEM1 AMOUNT DESCRIPTION ITEM2
A 5 aaaaaaa A001
A 12 aaaaaaa A002
A 10 aaaaaaa A003
PLEASE ANYONE HELP ME....................
This is i wrote query... but till not work well
DECLARE @tbl TABLE(
RowID INT IDENTITY(1, 1),
DESCRIPTION char(200),
ITEM1 char(10),
--UNIT_OF_MEASURE_CODE1 char(20),
--QUANTITY1 decimal (9),
--RATE money,
--AMOUNT money,
ITEM2 char(10))
--INSERT @tbl SELECT DESCRIPTION,ITEM1,UNIT_OF_MEASURE_CODE1,QUANTITY1,RATE,AMOUNT FROM TEmp_tool
--INSERT @tbl select distinct description, item1,UNIT_OF_MEASURE_CODE1,QUANTITY1,RATE,AMOUNT,ITEM2 from TEmp_tool1 where item1 = 'A'
INSERT @tbl select distinct description, item1,ITEM2 from TEmp_tool1 where item1 = 'A'
--SELECT * FROM @TBL
declare @count int, /*create local @@fetch_status*/
@iRow int /*row pointer (index)*/
declare @num int
DECLARE @DESCRIPTION AS CHAR(100)
/*get array Upper Bound (highest ID number)*/
SET @count = @@ROWCOUNT
/*initialize index counter*/
SET @iRow = 1
/*establish loop structure*/
WHILE @iRow <= @count
BEGIN
set @num=@iRow
/*get row values*/
UPDATE @tbl SET ITEM2 =@num WHERE RowID=@iRow --AND (DESCRIPTION=description) AND (item1=ITEM1)
--insert into TEMP_TOOL1 (ITEM2) VALUES (@num) WHERE (description=DESCRIPTION) AND (ITEM1='A')
--UPDATE TESTTBL SET ITEM2=@num WHERE (description=DESCRIPTION) AND (ITEM1='A')
SET @DESCRIPTION=(SELECT DESCRIPTION FROM @tbl WHERE RowID=@iRow )
INSERT INTO TESTTBL (ITEM2,item1,DESCRIPTION)
VALUES (@num,'A',@DESCRIPTION)
SET @iRow = @iRow + 1
END
SELECT * FROM @TBL
PRINT @DESCRIPTION
November 9, 2008 at 9:59 am
Please refer to the post in my signature for an example of how you can supply table DDL and sample data. Provide that information, with the result you're looking for for your dataset, and we can help you find a solution. Make sure you explain how you want to handle Amount.
November 9, 2008 at 11:22 am
How are you getting the new name? It it just the letter plus the row number? You need a tally type table to do this in 2000.
November 9, 2008 at 8:12 pm
hi...
My new row number is letter + Row number. I tried to do " Get all rows and get repeated rows and sort. then giving new name to above rows.
Thank you
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply