SQL Server 2000 中的行到列 [英] Rows to Columns in SQL Server 2000
本文介绍了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.. | ...
我阅读了很多关于这方面的内容,但我发现它很难实现,因为我从未使用过游标,并且 SQL Server 2000 中不提供数据透视表,我希望这里有人可以帮助我,谢谢.
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 没有 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 Server 2000 中的行到列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文