SQL Server 2000中的行到列 [英] Rows to Columns in SQL Server 2000

问题描述

我有这个..

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  

我想有这个...

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.

推荐答案

由于SQL Server 2000中的游标和枢轴在SQL Server 2000中不可用，我希望有人在这里可以帮助我。没有 PIVOT 函数，您应该能够使用类似于以下内容的

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)

SQL小提示演示

这篇关于SQL Server 2000中的行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！