cursor - Rows to Columns in SQL Server 2000

Question

I have this..

IDProspecto | IDObservacionCustomer | Observacion
---------------------------------------------------------    
  2204078   | 275214                | 03/9 Hable con Claudia me informa que Roberto ya se termino le deje..
  2204078   | 294567                | 19/09 SOLICITAN LLAME MA?ANA A ALEJANDRO   
  2204078   | 295310                | 20/09 se envia mail a adrian 
  2204078   | 304102                | CIA SOLICITA NO INSTALE EQUIPO  

And I want to have this...

idprospecto | observacion1            | observacion2            | observacion3      | observacion4 | observacionN
-----------------------------------------------------------------------------------------    
  2204078   | 03/09 Hable con clau... | 19/09 solicitan llame... | 20/09 se envia... | CIA solicita..   | ...

I read a lot about this but I found it hard to implement, since I never used cursor and pivot is not available in SQL Server 2000, I hope someone here can help me, thanks.

Answer 1

Since SQL Server 2000 does not have the PIVOT function, you should be able to use something similar to the following:

DECLARE @query AS NVARCHAR(4000)
DECLARE   @rowCount as int
DECLARE   @pivotCount as int
DECLARE   @pivotRow as varchar(10)

set @rowCount = 1
set @pivotRow = ''

create table #colsPivot
(
  id int IDENTITY(1,1),
  name varchar(20),
  CustId int
)

insert into #colsPivot
select 'Observacion', IDObservacionCustomer
from yourtable

set @pivotCount= (select COUNT(*) from #colsPivot) 

-- reset rowcount
set @rowCount = 1
set @query = ''

---- create the CASE string
while @rowCount <= @pivotCount
    begin
        set @pivotRow = (select Top 1 CustId from #colsPivot)

        set @query = @query + ', max(case when IDObservacionCustomer = ''' + @pivotRow + ''' then Observacion end) as ''Observacion_' + cast(@rowCount as varchar(10)) + ''''

        delete from #colsPivot where CustId = @pivotRow

        if @rowCount <= @pivotCount
            begin
                set @rowCount = @rowCount + 1
            end
    end

-- add the rest of the SQL Statement
set @query = 'SELECT IDProspecto ' + @query + ' from yourtable group by IDProspecto'

exec(@query)

See SQL Fiddle With Demo