应该是你要的了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 #Tempdeclare @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=@Valuecreate 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+''''
把显示转为字符加起来..
如:
rtrim(1)+':'+'aa'
谢谢,可是我是能保存的进去,关键是要读取出来的排列要跟保存进去的排列是一样的。。
如果让你们就按excel一个sheet网格中的所有数据保存到一个固定表的字段中,又要如何读取还原成为原先excel中的sheet的格式呢?
( 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 #Tempdeclare @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=@Valuecreate 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