22,209
社区成员
发帖
与我相关
我的任务
分享
create table #Temp
( ID char(1) not null,
row int not null,
col int not null,
Value int not null
)
insert #Temp values('A',1,1,5)
insert #Temp values('A',2,2,4)
insert #Temp values('A',8,8,6)
select * from #Temp
declare @RowCount int
declare @ColCount int
declare @Row int
declare @Col int
declare @Value char(1)
select @Value='A'
select @Row=1
select @Col=1
select @RowCount=max(row),@ColCount=max(col) from #Temp where ID=@Value
create table #Cube(Row int not null)
while @Row<=@RowCount
begin
insert #Cube values(@Row)
select @Row=@Row+1
end
declare @SQL nvarchar(4000)
select @SQL='select '
while @Col<@ColCount
begin
select @SQL=@SQl+'(case when #Temp.col='+cast(@Col as varchar)+ 'then #Temp.Value end) as COL'+cast(@Col as varchar)+','
select @Col=@Col+1
end
select @SQL=@SQl+'(case when #Temp.col='+cast(@Col as varchar)+ 'then #Temp.Value end) as COL'+cast(@Col as varchar)
select @SQL=@SQl+' from #Cube
left outer join #Temp
on #Cube.Row=#Temp.row
and #Temp.ID='''+@Value+''''
exec(@SQL)
drop table #Temp
drop table #Cube