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.
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)