DECLARE Item_Cursor SCROLL CURSOR FOR SELECT ItemType,ItemtypeID FROM #TMP01
OPEN Item_Cursor
FETCH Item_Cursor INTO @ItemType,@ItemtypeID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT #Result(TypeID,ItemID,ItemTypeID,ItemType,ItemName)
SELECT TOP 1 @TypeID,ItemID,@ItemtypeID,@ItemType,ItemName FROM table1 WHERE ItemType=@ItemType
FETCH Item_Cursor INTO @ItemType,@ItemtypeID
END
CLOSE Item_Cursor
DEALLOCATE Item_Cursor
INSERT INTO #Result(TypeID,ItemID,ItemTypeID,ItemType,ItemName)
SELECT 1,ItemID,ItemtypeID,ItemType,ItemName FROM table1
WHERE ItemID NOT IN (SELECT itemID from #Result)
UPDATE #Result SET ItemType=NULL WHERE TypeID<>0
SELECT ItemType,ItemName FROM #Result ORDER BY ItemTypeID,TypeID