--exec sp_getcrossdata
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO - exec sp_getcrossdata - alter proc sp_getcrossdata as - begin declare @SQLString nvarchar (4000) declare @temp_cybh varchar (50) declare @temp_gzyd varchar (511)
Select User Name, Name INTO # TMP1 from User Table SELECT Word Number, Work Category '|' Work Points AS Work Point INTO # TMP2 From Word View Select User Name, Word Number, Sum (Working) AS Work InTo # TMP3 from loging fine table Where project number = '0000000' Group by Username, Word Number ORDER BY Word Number
Set @Sqlstring = N'SELECT Name, 'DECLARE CURSOR_XKB CURSOR for Select Distinct a. Word Number, Work Point from # TMP3 AS A, #TMP2 AS B WHERE A. Word Number = B. Word No. Open Cursor_xkb Fetch Next from Cursor_xkb Into @ Temp_cybh, @ Temp_gzyd While @@ fetch_status = 0 Begin Set @ Sqlstring = @ Sqlstring N'isnull (SUM (Case B. Word No. When '' Cast (@temp_cybh as varchar) n '' 'THEN work end), 0) as " ' @ temp_gzyd '", 'fetch next from cursor_xkb into @ temp_cybh, @ temp_gzyd end close cursor_xkb deallocate cursor_xkb set @SQLString = left (@ SQLString, len (@SQLString) -1) set @SQLString = @Sqlstring N'From # TMP1 AS A, # TMP3 AS B WHERE A. User name = b. User name group by name 'print @sqlstring exec sp_executesql @sqlstring Drop Table # TMP1, # TMP2, # TMP3 - END
Goset quoted_identifier off goset ANSI_NULLS ON Go
