游标、获取本地本地多个文件、Excel数据导入、跨服务器数据拷贝、行转列示例

declare @dirPath varchar(200)='C:\Users\Administrator\Desktop\待处理数据\顺江学校4\'

--------------------------------------------------------------------------------------------获取本地文件夹下多个文件----------------------------------------------------------
if(OBJECT_ID('ff')>0)
    drop table ff


create table ff
(
    id int identity(1,1),
    fName varchar(300),
    depth int,
    isFile int
)

insert into ff
exec xp_dirtree @dirPath, 0, 1

--select * from ff

if(OBJECT_ID('RawScore')>0)
    drop table RawScore
    
CREATE TABLE [dbo].[RawScore](
    [F] [varchar](50) NULL,
    [F0] [varchar](50) NULL,
    [F1] [nvarchar](255) NULL,
    [F2] [nvarchar](255) NULL,
    [F3] [float] NULL,
    [F4] [float] NULL,
    [F5] [float] NULL,
    [F6] [nvarchar](255) NULL,
    [F7] [float] NULL,
    [F8] [float] NULL,
    [F9] [float] NULL
)

declare @studentId varchar(50)='',@studentName varchar(50)='',@studentInfo varchar(100)='';
declare @pos int=0,@len int =0;
declare @fileName varchar(100);
declare @sql varchar(max) = '';

------------------------------------------------------------------游标操作-----------------------------------------------------------------------------
declare cur cursor for
select fName from ff

open cur
fetch next from cur into @fileName

while @@FETCH_STATUS=0
begin

    set @studentInfo=SUBSTRING(@fileName,1,patindex('%.xls',@fileName)-1)
    set @pos = PATINDEX('%[_]%',@studentInfo);
    set @len = LEN(@studentInfo);
    set @studentName = SUBSTRING(@studentInfo,1,@pos-1);
    set @studentId = SUBSTRING(@studentInfo,@pos+1,@len);
    --select @studentName,@studentId

--------------------------------------------------------------------------------------导入本地Excel文件数据---------------------------------------------------------------------------
    set @sql = 'insert into RawScore
    select '''+@studentId+''','''+@studentName+''',* from OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database='+@dirPath+@fileName+''', [Sheet1$])';
    exec(@sql)
    
    fetch next from cur into @fileName
    
end

close cur
deallocate cur

if(OBJECT_ID('StudentScore')>0)
    drop table StudentScore
    
CREATE TABLE [dbo].[StudentScore](
    [Id] [uniqueidentifier] NOT NULL,
    [StudentID] [varchar](50) NULL,
    [Name] [varchar](50) NULL,
    [Kind] [int] NULL,
    [ItemName] [varchar](50) NULL,
    [Score] [float] NULL
)

insert into studentScore
select NEWID(), f,F0,1,F2,f5 from RawScore where f2!='内容板块(满分)' and PATINDEX('%[(]%',F2)>0
union all
select NEWID(), f,F0,2,F6,f9 from RawScore where f6!='能力层次(满分)' and PATINDEX('%[(]%',F6)>0

select * from StudentScore order by name

-------------------------------------------------------跨服务器链接数据库进行数据操作-------------------------------------------------
--declare @count int=0
    
--select @count=COUNT(*) from sys.servers where name='syncDBLink'
--if(@count > 0)
--begin
--    exec sp_dropserver 'syncDBLink','droplogins'
--end

----打开指定服务器上的数据库
--exec sp_addlinkedserver 'syncDBLink','','SQLOLEDB','192.168.0.102','','','wangyue0428';
--exec sp_addlinkedsrvlogin 'syncDBLink',false,null,'sa','HX1q2w3e4r';
--exec sp_serveroption 'syncDBLink','rpc out','true';


--delete from  syncDBLink.wangyue0428.dbo.StudentScore

--insert into syncDBLink.wangyue0428.dbo.StudentScore
--select * from StudentScore

--exec sp_dropserver 'syncDBLink','droplogins'


--select StudentID,Name,Kind,SUM(Score) from StudentScore
--group by StudentID,Name,Kind

--select distinct studentId,name from StudentScore

-----------------------------------------------------------------------------------行转列实现-------------------------------------------------------------------
;with cte1 as
(
    select StudentID,Name, [拼音练习(8.00)],[字词练习(16.00)],[句子练习(21.00)],[课内文段阅读(12.00)],[课外文段阅读(18.00)],[习作(25.00)]
    ,[拼音练习(8.00)]+[字词练习(16.00)]+[句子练习(21.00)]+[课内文段阅读(12.00)]+[课外文段阅读(18.00)]+[习作(25.00)] as 小计
    from 
    (
        select StudentID,Name,ItemName,Score from syncDBLink.wangyue0428.dbo.StudentScore where Kind=1
    ) as s
    pivot
    (
        sum(Score)
        for ItemName in([拼音练习(8.00)],[字词练习(16.00)],[句子练习(21.00)],[课内文段阅读(12.00)],[课外文段阅读(18.00)],[习作(25.00)])
    ) as pv
)
,cte2
as
(
    select StudentID,Name, [识记(18.00)],[表达应用(51.00)],[理解(16.00)],[分析综合(15.00)]
    ,[识记(18.00)]+[表达应用(51.00)]+[理解(16.00)]+[分析综合(15.00)] as 小计
    from 
    (
        select StudentID,Name,ItemName,Score from syncDBLink.wangyue0428.dbo.StudentScore where Kind=2
    ) as s
    pivot
    (
        sum(Score)
        for ItemName in([识记(18.00)],[表达应用(51.00)],[理解(16.00)],[分析综合(15.00)])
    ) as pv
)
select ROW_NUMBER() over(order by cte1.小计 desc) as 序号, '顺江中学' as 学校名称,cte1.Name as 姓名,'' as 性别,[拼音练习(8.00)],[字词练习(16.00)],[句子练习(21.00)],[课内文段阅读(12.00)],[课外文段阅读(18.00)],[习作(25.00)],cte1.小计
,[识记(18.00)],[表达应用(51.00)],[理解(16.00)],[分析综合(15.00)],cte2.小计
from cte1
inner join cte2 on cte1.StudentID=cte2.StudentID