Help with Stored procedure(SQL 2005)

HI experts,
I am seeking for some help in the following SP.

e.g, When I pass '10' to the following proc,
Village       Khate No       Khatedars                                       Survey No       Area Acquired       
Kalkuhi       10             Dehankar Gunderao,Dehankar Rambhau       7               0.88             
Kalkuhi       10             Dehankar Gunderao,Dehankar Rambhau       8               0.87             

I want to hide the Village name,jhate no and khatedar names in the o/p
I mean it should be like this.
Village       Khate No       Khatedars                                          Survey No       Area Acquired       
Kalkuhi       10             Dehankar Gunderao,Dehankar Rambhau       7                 0.88             
                                                                                        8                 0.87             



Create PROCEDURE [dbo].[spKhateAcquisition_new]
      -- Add the parameters for the stored procedure here
      @khateno nvarchar(100) = null,
      @villageid int = null,
      @lastname nvarchar(100) = null,
    @firstname nvarchar(100) = null,
    @middlename nvarchar(100) = null
      
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
Declare @kno nvarchar(100)
DECLARE @khatenames nVARCHAR(4000)


SET NOCOUNT ON;

    -- Insert statements for procedure here

      CREATE TABLE #temp
      (
      [khatedar_no] [nvarchar](100) NULL,
      [Khatedars] [nvarchar](1000) NULL,
      [new_survey_no] [nvarchar](50) NULL,
      [Village_Name] [nvarchar](50) NULL,
      [area_acquired] [float] NULL,
      [net_amount] [float] NULL
      )

Insert into #temp
Select distinct sk.khatedar_no,'xxxx' Khatedars,sk.new_servey_no,v.village_name,(s.area_acquired*sk.share_if_any)/100 as area_acquired,(s.net_amount*sk.share_if_any)/100
from tbperson p left outer join tbSurveryKhatedar sk on p.person_no = sk.person_no
left outer join tbGaothanPerson gp on p.person_no = gp.person_no
inner join tbVillage v on v.village_id = sk.village_id
inner join tbSurvey s on s.village_id = sk.village_id And sk.new_servey_no = s.new_survey_no
Where sk.Village_id = Coalesce(@villageid,sk.Village_id)
And        sk.khatedar_no =       Coalesce(@khateno,khatedar_no)
And        p.last_name = Coalesce(@lastname,last_name)
And        p.first_name = Coalesce(@firstname,first_name)
And        p.middle_name = Coalesce(@middlename,middle_name)

DECLARE db_cursor CURSOR FOR
      SELECT khatedar_no
      FROM #temp

      OPEN db_cursor  
      FETCH NEXT FROM db_cursor INTO @kno

WHILE @@FETCH_STATUS = 0  
BEGIN  
      Set @khatenames = null

            SELECT  @khatenames = ISNULL(@khatenames + ',', '') +  CAST (p.last_name+' '+p.first_name AS nVARCHAR(150))
            FROM tbPerson p
            Inner Join tbSurveryKhatedar sk
            On sk.person_no = p.person_no
            WHERE sk.khatedar_no =  Coalesce(@kno,khatedar_no) And sk.village_id = Coalesce(@villageid,village_id)
            GROUP BY p.last_name, p.first_name

      --      SELECT @khatenames AS new_survey_no    

            Update #temp set khatedars = @khatenames where khatedar_no = @kno
            
            FETCH NEXT FROM db_cursor INTO @kno
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

Select * from #temp

END


LVL 8
Johny BravoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
sql server 2005, you can do that without a cursor
select case when r = 1 then village else null end as Village
     , case when r = 1 then khate_no else null end as khate_no
     , case when r = 1 then Khatedars else null end as Khatedars
     , Survey_no
     , Area_Acquired 
FROM
(
select village, khate_no, Khatedars, Survey_No, Area_Acquired 
    , row_number() over (partition by village order by survey_no) r
  from yourtable
) sq
order by Village, r

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.