当前位置：Gxlcms >
数据库问题 >
sql2000行转列 转过来的测试完也不知那个网站去哪了 没法写出处了
sql2000行转列 转过来的测试完也不知那个网站去哪了 没法写出处了
时间：2021-07-01 10:21:17
帮助过：10人阅读
procedure dbo.CommonRowToCol
@sql nvarchar(
4000)
as
begin
--必须包含colname列和result列(不区分大小写),
--除colname列和result列 其余各列均会作为判别记录唯一性的条件
--使用时,只需将要作为列名的字段定义为colname,将欲显示的列定义为result即可
--如select student as 学生 ,course as colname,score result from class
create table #
temp(
[序号] int IDENTITY(
1,
1))
declare @sqlTemp nvarchar(
4000),
@sql_select nvarchar(
1000),
@temp nvarchar(
500)
declare @sql_All nvarchar(
4000),
@sql_Table nvarchar(
1000),
@sql_declare nvarchar(
1000),
@sql_Fetch nvarchar(
1000) ,
@sql_id nvarchar(
1000),
@sql_id2 nvarchar(
1000),
@sql_insert nvarchar(
1000),
@sql_value nvarchar(
1000)
set @sql_select = ‘‘ set @sql_All = ‘‘ set @sql_Table =‘‘ set @sql_declare = ‘‘ set @sql_Fetch = ‘‘
set @sql_id = ‘‘ set @sql_id2 = ‘‘ set @sql_insert=‘‘ set @sql_value = ‘‘
set @sql = lower(
LTRIM(
@sql))
set @sqlTemp = @sql
set @sqlTemp = ‘ select top 0 * into #tempTbl from (‘+@sqlTemp+‘)z ‘+
‘ select @temp = Name+‘‘,‘‘+@temp from ( select top 1111 name from tempdb..syscolumns where id = object_id(N‘‘tempdb..#tempTbl‘‘) order by colorder )z ‘
EXECUTE sp_executesql
@sqlTemp, N
‘@temp nvarchar(1000) output‘,
@sql_select output
while charindex(
‘,‘,
@sql_select)
> 0
begin
select @temp = lower(
LEFT(
@sql_select,
charindex(
‘,‘,
@sql_select)
-1))
select @sql_select = stuff(
@sql_select,
1,
charindex(
‘,‘,
@sql_select),
‘‘)
if (
@temp <> ‘colname‘)
and (
@temp<>‘result‘)
begin
set @sql_Table = ‘ [‘+@temp+‘] nvarchar(1000),‘ + @sql_Table
set @sql_insert = ‘ [‘+@temp+‘],‘ + @sql_insert
set @sql_value =‘isnull(@‘+@temp+‘,‘‘‘‘),‘+@sql_value
set @sql_id = ‘ [‘+@temp+‘] = isnull(@‘+@temp+‘,‘‘‘‘) and‘ + @sql_id
set @sql_id2 = ‘ [‘+@temp+‘] = isnull(‘‘‘‘‘‘+@‘+@temp+‘+‘‘‘‘‘‘ ,‘‘‘‘‘‘‘‘) and‘ + @sql_id2
end;
set @sql_declare = ‘ @‘+@temp+‘ nvarchar(1000),‘+@sql_declare
set @sql_Fetch = ‘ @‘+@temp+‘,‘+@sql_Fetch
end;
set @sql_Table = ‘ALTER TABLE #temp ADD ‘ +left(
@sql_Table,
len(
@sql_table)
-1)
+‘ ‘
set @sql_declare = ‘declare @sqlTemp nvarchar(4000),‘ +left(
@sql_declare,
len(
@sql_declare)
-1)
+ ‘ ‘
set @sql_Fetch = left(
@sql_Fetch,
len(
@sql_fetch)
-1)
+ ‘ ‘
set @sql_id = left(
@sql_id,
len(
@sql_id)
-3)
+ ‘ ‘
set @sql_id2 = left(
@sql_id2,
len(
@sql_id2)
-3)
+ ‘ ‘
set @sql_insert = left(
@sql_insert,
len(
@sql_insert)
-1)
+ ‘ ‘
set @sql_value = left(
@sql_value,
len(
@sql_value)
-1)
+ ‘ ‘
set @sql_All = @sql_declare +
‘ Declare myCur Cursor For ‘+ @sql + ‘ Open myCur Fetch NEXT From myCur Into ‘+ @sql_Fetch+
‘ While @@fetch_status=0 Begin ‘+
‘ if not exists(select * from tempdb..syscolumns where id = object_id(N‘‘tempdb..#temp‘‘) and name = @colName)
begin
set @sqlTemp =‘‘alter table #temp add [‘‘+@colName+‘‘] nvarchar(4000) ‘‘
exec(@sqlTemp)
end
if not exists(select * from #temp where ‘+@sql_id+‘)
begin
insert into #temp(‘+@sql_insert+‘) values(‘+@sql_value+‘)
end
set @sqlTemp =‘‘ update #temp set [‘‘+@colName+‘‘] = isnull(‘‘‘‘‘‘+@result+‘‘‘‘‘‘,‘‘‘‘‘‘‘‘) where ‘ + @sql_id2+‘‘‘
exec(@sqlTemp)
Fetch NEXT From myCur Into ‘+ @sql_Fetch+
‘end
Close myCur
Deallocate myCur
select * from #temp
‘
exec (
@sql_Table)
exec(
@sql_All)
end
sql2000行转列 转过来的测试完也不知那个网站去哪了 没法写出处了
标签：value ide comm ext and tab alt 区分 row
