SQL server 行列转换

DECLARE @sql VARCHAR(8000)


SET @sql=''  --初始化变量@sql


SELECT @sql=@sql+','+dates FROM InOutDoor3 GROUP BY dates
order by dates --变量多值赋值


SET @sql=stuff(@sql,1,1,'')--去掉首个','


SET @sql='select ids, '+@sql+' from InOutDoor3 pivot (max(firstintime) for dates in ('+@sql+'))a'


exec(@sql)






--firstin
DECLARE @sql VARCHAR(8000)


DECLARE @firstin VARCHAR(8000) 


SET @sql=''  --初始化变量@sql
SET @firstin=''  --初始化变量@firstin


SELECT @sql=@sql+','+dates FROM InOutDoor3 GROUP BY dates
order by dates --变量多值赋值
SELECT @firstin=@firstin+','+dates+' as '+dates+'firstin' FROM InOutDoor3 GROUP BY dates
order by dates --变量多值赋值


SET @sql=stuff(@sql,1,1,'')--去掉首个','
SET @firstin=stuff(@firstin,1,1,'')--去掉首个','


SET @sql='select ids, '+@firstin+' from InOutDoor3 pivot (max(firstintime) for dates in ('+@sql+'))a'


exec(@sql)


--fitstout
DECLARE @sql VARCHAR(8000)


DECLARE @firstout VARCHAR(8000) 


SET @sql=''  --初始化变量@sql
SET @firstout=''  --初始化变量@firstout


SELECT @sql=@sql+','+dates FROM InOutDoor3 GROUP BY dates
order by dates --变量多值赋值
SELECT @firstout=@firstout+','+dates+' as '+dates+'firstout' FROM InOutDoor3 GROUP BY dates
order by dates --变量多值赋值


SET @sql=stuff(@sql,1,1,'')--去掉首个','
SET @firstout=stuff(@firstout,1,1,'')--去掉首个','


SET @sql='select ids, '+@firstout+' from InOutDoor3 pivot (max(firstouttime) for dates in ('+@sql+'))a'


exec(@sql)




firstinout


DECLARE @sql VARCHAR(8000)
DECLARE @firstin VARCHAR(8000) 
DECLARE @sqlfirstin VARCHAR(8000) 
DECLARE @firstout VARCHAR(8000)
DECLARE @sqlfirstout VARCHAR(8000) 
DECLARE @allinout VARCHAR(8000)
declare @sqlallinout varchar(8000)


SET @sql=''  --初始化变量@sql
SET @firstin=''  --初始化变量@firstin
set @sqlfirstin=''  --初始化变量@sqlfirstin
SET @firstout=''  --初始化变量@firstout
set @sqlfirstout=''  --初始化变量@sqlfirstout
set @allinout=''  --初始化变量@allinout
set @sqlallinout=''


SELECT @sql=@sql+','+dates FROM InOutDoor3 GROUP BY dates
order by dates --变量多值赋值
SELECT @firstin=@firstin+',max('+dates+') as '+dates+'firstin' FROM InOutDoor3 GROUP BY dates
order by dates --变量多值赋值
SELECT @firstout=@firstout+',max('+dates+') as '+dates+'firstout' FROM InOutDoor3 GROUP BY dates
order by dates --变量多值赋值
select @allinout=@allinout+',fin.'+dates+'firstin, fout.'+dates+'firstout' FROM InOutDoor3 GROUP BY dates
order by dates






SET @sql=stuff(@sql,1,1,'')--去掉首个','
SET @firstin=stuff(@firstin,1,1,'')--去掉首个','
SET @firstout=stuff(@firstout,1,1,'')--去掉首个','
set @allinout=stuff(@allinout,1,1,'')--去掉首个','


SET @sqlfirstin='select ids, '+@firstin+' from InOutDoor3 pivot (max(firstintime) for dates in ('+@sql+'))a
group by ids'
SET @sqlfirstout='select ids, '+@firstout+' from InOutDoor3 pivot (max(firstouttime) for dates in ('+@sql+'))a
group by ids'


set @sqlallinout = 'select fin.ids, '+@allinout+' from ('+@sqlfirstin+') fin
left join ('+@sqlfirstout+') fout on  fin.ids=fout.ids order by fin.ids'


print @sqlallinout


exec(@sqlallinout)




allinout


DECLARE @sql VARCHAR(8000)
DECLARE @firstin VARCHAR(8000) 
DECLARE @sqlfirstin VARCHAR(8000) 
DECLARE @firstout VARCHAR(8000)
DECLARE @sqlfirstout VARCHAR(8000)
DECLARE @allinout VARCHAR(8000)
DECLARE @lastin VARCHAR(8000) 
DECLARE @sqllastin VARCHAR(8000) 
DECLARE @lastout VARCHAR(8000)
DECLARE @sqllastout VARCHAR(8000)
declare @sqlallinout varchar(8000)


SET @sql=''  --初始化变量@sql
SET @firstin=''  --初始化变量@firstin
set @sqlfirstin=''  --初始化变量@sqlfirstin
SET @firstout=''  --初始化变量@firstout
set @sqlfirstout=''  --初始化变量@sqlfirstout
SET @lastin=''  --初始化变量@lastin
set @sqllastin=''  --初始化变量@sqllastin
SET @lastout=''  --初始化变量@lastout
set @sqllastout=''  --初始化变量@sqllastout
set @allinout=''  --初始化变量@allinout
set @sqlallinout=''


SELECT @sql=@sql+','+dates FROM InOutDoor3 GROUP BY dates
order by dates --变量多值赋值
SELECT @firstin=@firstin+',max('+dates+') as '+dates+'firstin' FROM InOutDoor3 GROUP BY dates
order by dates --变量多值赋值
SELECT @firstout=@firstout+',max('+dates+') as '+dates+'firstout' FROM InOutDoor3 GROUP BY dates
order by dates --变量多值赋值
SELECT @lastin=@lastin+',max('+dates+') as '+dates+'lastin' FROM InOutDoor3 GROUP BY dates
order by dates --变量多值赋值
SELECT @lastout=@lastout+',max('+dates+') as '+dates+'lastout' FROM InOutDoor3 GROUP BY dates
order by dates --变量多值赋值
select @allinout=@allinout+',fin.'+dates+'firstin, fout.'+dates+'firstout'+',lin.'+dates+'lastin, lout.'+dates+'lastout' FROM InOutDoor3 GROUP BY dates
order by dates






SET @sql=stuff(@sql,1,1,'')--去掉首个','
SET @firstin=stuff(@firstin,1,1,'')--去掉首个','
SET @firstout=stuff(@firstout,1,1,'')--去掉首个','
SET @lastin=stuff(@lastin,1,1,'')--去掉首个','
SET @lastout=stuff(@lastout,1,1,'')--去掉首个','
set @allinout=stuff(@allinout,1,1,'')--去掉首个','


SET @sqlfirstin='select dept,ids,name, '+@firstin+' from InOutDoor3 pivot (max(firstintime) for dates in ('+@sql+'))a
group by dept,ids,name'
SET @sqlfirstout='select ids, '+@firstout+' from InOutDoor3 pivot (max(firstouttime) for dates in ('+@sql+'))a
group by ids'
SET @sqllastin='select ids, '+@lastin+' from InOutDoor3 pivot (max(lastintime) for dates in ('+@sql+'))a
group by ids'
SET @sqllastout='select ids, '+@lastout+' from InOutDoor3 pivot (max(lastouttime) for dates in ('+@sql+'))a
group by ids'


set @sqlallinout = 'select fin.dept,fin.ids,fin.name, '+@allinout+' from ('+@sqlfirstin+') fin
left join ('+@sqlfirstout+') fout on  fin.ids=fout.ids 
left join ('+@sqllastin+') lin on  fin.ids=lin.ids 
left join ('+@sqllastout+') lout on  fin.ids=lout.ids 
order by fin.dept,fin.ids'


--创建视图
--set @sqlallinout = 'create view li002 as (select fin.dept,fin.ids,fin.name, '+@allinout+' from ('+@sqlfirstin+') fin
--left join ('+@sqlfirstout+') fout on  fin.ids=fout.ids 
--left join ('+@sqllastin+') lin on  fin.ids=lin.ids 
--left join ('+@sqllastout+') lout on  fin.ids=lout.ids)'




exec(@sqlallinout)