Database

BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_MyTestingTable
	(
	TestCol varchar(50) NULL
	)  ON [PRIMARY]

INSERT INTO dbo.Tmp_MyTestingTable (TestCol)
		SELECT DISTINCT TestCol FROM dbo.MyTestingTable TABLOCKX

DROP TABLE dbo.MyTestingTable

EXECUTE sp_rename 'dbo.Tmp_MyTestingTable', 'MyTestingTable'

COMMIT

DECLARE @cCol varchar(50)

DECLARE MyCursor CURSOR FOR
    SELECT testcol
    FROM MyTestingTable 

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @cCol
WHILE @@FETCH_STATUS = 0
BEGIN
	if(SELECT COUNT(*) FROM MyTestingTable WHERE TestCol = @cCol)>1
	BEGIN
		DELETE FROM MyTestingTable
			WHERE CURRENT OF MyCursor
	END
	FETCH NEXT FROM MyCursor INTO @cCol
END
CLOSE MyCursor
DEALLOCATE MyCursor
GO

BalasahebK wrote:

Yes I'm using Varchar in my table
still I would like to know, is there any way to convert such word in some proper format?

CREATE FUNCTION dbo.GetPopTotal(@cParent varchar(50))
RETURNS int AS  
BEGIN 
declare @nTotal int
declare @nGrandTotal int
declare @cChild varchar(50)

set @nTotal = 0

DECLARE  childcursor CURSOR FOR 
SELECT DISTINCT child,pop 
FROM travel
WHERE parent = @cParent

OPEN childcursor
FETCH NEXT FROM childcursor INTO @cChild, @nGrandTotal 
WHILE @@FETCH_STATUS = 0
BEGIN
	select @nGrandTotal = @nGrandTotal + @nTotal + dbo.GetPopTotal(@cChild)

	FETCH NEXT FROM childcursor INTO @cChild, @nTotal 
END

CLOSE childcursor
DEALLOCATE childcursor

return isnull(@nGrandTotal,0)

END

select parent,child, dbo.GetPopTotal(child) + pop  as PopTotal from travel