sql server动态行转列

lyy4590 2020-08-30 07:10:43

大佬们，如图所示，左边红框是源数据，我想通过行转列成右边红框，根据a1，a2 group by，请问该怎么搞？

...全文

82 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

锟斤拷锟斤拷 2020-12-07

打赏
举报

引用 2 楼雨夹雪的回复:



--插入测试数据
select '基层员工'  as '范围',21826  as '在职人数',497  as '离职人数',201812  as '时间' into #T
union all
select '主管级及以上员工'  as '范围',3904  as '在职人数',40  as '离职人数',201812  as '时间'
union all
select '基层员工'  as '范围',21835 as '在职人数',488 as '离职人数',201901 as '时间'
union all
select '主管级及以上员工'  as '范围',3883 as '在职人数',19 as '离职人数',201901 as '时间'
union all
select '基层员工'  as '范围',21819 as '在职人数',472 as '离职人数',201902 as '时间'
union all
select '主管级及以上员工'  as '范围',3900 as '在职人数',36 as '离职人数',201902 as '时间'
union all
select '基层员工'  as '范围',22095 as '在职人数',748 as '离职人数',201903 as '时间'
union all
select '主管级及以上员工'  as '范围',3914 as '在职人数',50 as '离职人数',201903 as '时间'
union all
select '基层员工'  as '范围',22006 as '在职人数',659 as '离职人数',201904 as '时间'
union all
select '主管级及以上员工'  as '范围',3927 as '在职人数',63 as '离职人数',201904 as '时间'
union all
select '基层员工'  as '范围',22080 as '在职人数',733 as '离职人数',201905 as '时间'
union all
select '主管级及以上员工'  as '范围',3935 as '在职人数',71 as '离职人数',201905 as '时间'
union all
select '基层员工'  as '范围',22016 as '在职人数',669 as '离职人数',201906 as '时间'
union all
select '主管级及以上员工'  as '范围',3935 as '在职人数',71 as '离职人数',201906 as '时间'
union all
select '基层员工'  as '范围',22039 as '在职人数',692 as '离职人数',201907 as '时间'
union all
select '主管级及以上员工'  as '范围',3911 as '在职人数',47 as '离职人数',201907 as '时间'
union all
select '基层员工'  as '范围',22061 as '在职人数',714 as '离职人数',201908 as '时间'
union all
select '主管级及以上员工'  as '范围',3934 as '在职人数',70 as '离职人数',201908 as '时间'
union all
select '基层员工'  as '范围',21904 as '在职人数',557 as '离职人数',201909 as '时间'
union all
select '主管级及以上员工'  as '范围',3913 as '在职人数',49 as '离职人数',201909 as '时间'
union all
select '基层员工'  as '范围',21905 as '在职人数',558 as '离职人数',201910 as '时间'
union all
select '主管级及以上员工'  as '范围',3902 as '在职人数',38 as '离职人数',201910 as '时间'
union all
select '基层员工'  as '范围',21915 as '在职人数',568 as '离职人数',201911 as '时间'
union all
select '主管级及以上员工'  as '范围',3907 as '在职人数',43 as '离职人数',201911 as '时间'
union all
select '基层员工'  as '范围',21929 as '在职人数',482 as '离职人数',201912 as '时间'
union all
select '主管级及以上员工'  as '范围',3905 as '在职人数',41 as '离职人数',201912 as '时间'



--定义变量
DECLARE @sql VARCHAR(max)=''
DECLARE @filed VARCHAR(max)=''

--动态生成
SELECT 
@sql=@sql+'LEFT JOIN  #T '+时间1+' ON  a.范围 = '+时间1+'.范围 AND '+时间1+'.时间='''+时间2+'''',
@filed=@filed+','+时间1+'.在职人数'+','+时间1+'.离职人数'+','+时间1+'.时间'
 FROM 
(
SELECT QUOTENAME(时间) AS 时间1,CONVERT(VARCHAR(10),时间) AS 时间2  FROM #T GROUP BY 时间
) a

SET @sql='SELECT a.范围'+@filed+' FROM 
(
	SELECT 范围 FROM #T GROUP BY 范围
) a 
'+@sql

--执行
exec (@sql)

根据大佬今天的思路又重新思考了一下这个问题


declare @sql varchar(max) =''
select @sql = @sql+
',max(case when a.时间='+cast(t.时间 as varchar(20))+' then a.在职人数 else NULL end) 在职人数 '+
',max(case when a.时间='+cast(t.时间 as varchar(20))+' then a.离职人数 else NULL end) 离职人数 '+
',max(case when a.时间='+cast(t.时间 as varchar(20))+' then a.时间 else NULL end) 时间 '
from #T t group by t.时间
set @sql = 'select a.范围'+ @sql +'from #T a group by a.范围'
--set @sql = 'select * from #T'
print(@sql)
exec(@sql)

ダ雨夹雪リ 2020-11-27

打赏
举报



--插入测试数据
select '基层员工'  as '范围',21826  as '在职人数',497  as '离职人数',201812  as '时间' into #T
union all
select '主管级及以上员工'  as '范围',3904  as '在职人数',40  as '离职人数',201812  as '时间'
union all
select '基层员工'  as '范围',21835 as '在职人数',488 as '离职人数',201901 as '时间'
union all
select '主管级及以上员工'  as '范围',3883 as '在职人数',19 as '离职人数',201901 as '时间'
union all
select '基层员工'  as '范围',21819 as '在职人数',472 as '离职人数',201902 as '时间'
union all
select '主管级及以上员工'  as '范围',3900 as '在职人数',36 as '离职人数',201902 as '时间'
union all
select '基层员工'  as '范围',22095 as '在职人数',748 as '离职人数',201903 as '时间'
union all
select '主管级及以上员工'  as '范围',3914 as '在职人数',50 as '离职人数',201903 as '时间'
union all
select '基层员工'  as '范围',22006 as '在职人数',659 as '离职人数',201904 as '时间'
union all
select '主管级及以上员工'  as '范围',3927 as '在职人数',63 as '离职人数',201904 as '时间'
union all
select '基层员工'  as '范围',22080 as '在职人数',733 as '离职人数',201905 as '时间'
union all
select '主管级及以上员工'  as '范围',3935 as '在职人数',71 as '离职人数',201905 as '时间'
union all
select '基层员工'  as '范围',22016 as '在职人数',669 as '离职人数',201906 as '时间'
union all
select '主管级及以上员工'  as '范围',3935 as '在职人数',71 as '离职人数',201906 as '时间'
union all
select '基层员工'  as '范围',22039 as '在职人数',692 as '离职人数',201907 as '时间'
union all
select '主管级及以上员工'  as '范围',3911 as '在职人数',47 as '离职人数',201907 as '时间'
union all
select '基层员工'  as '范围',22061 as '在职人数',714 as '离职人数',201908 as '时间'
union all
select '主管级及以上员工'  as '范围',3934 as '在职人数',70 as '离职人数',201908 as '时间'
union all
select '基层员工'  as '范围',21904 as '在职人数',557 as '离职人数',201909 as '时间'
union all
select '主管级及以上员工'  as '范围',3913 as '在职人数',49 as '离职人数',201909 as '时间'
union all
select '基层员工'  as '范围',21905 as '在职人数',558 as '离职人数',201910 as '时间'
union all
select '主管级及以上员工'  as '范围',3902 as '在职人数',38 as '离职人数',201910 as '时间'
union all
select '基层员工'  as '范围',21915 as '在职人数',568 as '离职人数',201911 as '时间'
union all
select '主管级及以上员工'  as '范围',3907 as '在职人数',43 as '离职人数',201911 as '时间'
union all
select '基层员工'  as '范围',21929 as '在职人数',482 as '离职人数',201912 as '时间'
union all
select '主管级及以上员工'  as '范围',3905 as '在职人数',41 as '离职人数',201912 as '时间'



--定义变量
DECLARE @sql VARCHAR(max)=''
DECLARE @filed VARCHAR(max)=''

--动态生成
SELECT 
@sql=@sql+'LEFT JOIN  #T '+时间1+' ON  a.范围 = '+时间1+'.范围 AND '+时间1+'.时间='''+时间2+'''',
@filed=@filed+','+时间1+'.在职人数'+','+时间1+'.离职人数'+','+时间1+'.时间'
 FROM 
(
SELECT QUOTENAME(时间) AS 时间1,CONVERT(VARCHAR(10),时间) AS 时间2  FROM #T GROUP BY 时间
) a

SET @sql='SELECT a.范围'+@filed+' FROM 
(
	SELECT 范围 FROM #T GROUP BY 范围
) a 
'+@sql

--执行
exec (@sql)

qq_39174826 2020-11-27

打赏
举报

用一个最笨的方法，Case When，比较麻烦一点

--插入测试数据
select '基层员工'  as '范围',21826  as '在职人数',497  as '离职人数',201812  as '时间' into #T
union all
select '主管级及以上员工'  as '范围',3904  as '在职人数',40  as '离职人数',201812  as '时间'
union all
select '基层员工'  as '范围',21835 as '在职人数',488 as '离职人数',201901 as '时间'
union all
select '主管级及以上员工'  as '范围',3883 as '在职人数',19 as '离职人数',201901 as '时间'
union all
select '基层员工'  as '范围',21819 as '在职人数',472 as '离职人数',201902 as '时间'
union all
select '主管级及以上员工'  as '范围',3900 as '在职人数',36 as '离职人数',201902 as '时间'
union all
select '基层员工'  as '范围',22095 as '在职人数',748 as '离职人数',201903 as '时间'
union all
select '主管级及以上员工'  as '范围',3914 as '在职人数',50 as '离职人数',201903 as '时间'
union all
select '基层员工'  as '范围',22006 as '在职人数',659 as '离职人数',201904 as '时间'
union all
select '主管级及以上员工'  as '范围',3927 as '在职人数',63 as '离职人数',201904 as '时间'
union all
select '基层员工'  as '范围',22080 as '在职人数',733 as '离职人数',201905 as '时间'
union all
select '主管级及以上员工'  as '范围',3935 as '在职人数',71 as '离职人数',201905 as '时间'
union all
select '基层员工'  as '范围',22016 as '在职人数',669 as '离职人数',201906 as '时间'
union all
select '主管级及以上员工'  as '范围',3935 as '在职人数',71 as '离职人数',201906 as '时间'
union all
select '基层员工'  as '范围',22039 as '在职人数',692 as '离职人数',201907 as '时间'
union all
select '主管级及以上员工'  as '范围',3911 as '在职人数',47 as '离职人数',201907 as '时间'
union all
select '基层员工'  as '范围',22061 as '在职人数',714 as '离职人数',201908 as '时间'
union all
select '主管级及以上员工'  as '范围',3934 as '在职人数',70 as '离职人数',201908 as '时间'
union all
select '基层员工'  as '范围',21904 as '在职人数',557 as '离职人数',201909 as '时间'
union all
select '主管级及以上员工'  as '范围',3913 as '在职人数',49 as '离职人数',201909 as '时间'
union all
select '基层员工'  as '范围',21905 as '在职人数',558 as '离职人数',201910 as '时间'
union all
select '主管级及以上员工'  as '范围',3902 as '在职人数',38 as '离职人数',201910 as '时间'
union all
select '基层员工'  as '范围',21915 as '在职人数',568 as '离职人数',201911 as '时间'
union all
select '主管级及以上员工'  as '范围',3907 as '在职人数',43 as '离职人数',201911 as '时间'
union all
select '基层员工'  as '范围',21929 as '在职人数',482 as '离职人数',201912 as '时间'
union all
select '主管级及以上员工'  as '范围',3905 as '在职人数',41 as '离职人数',201912 as '时间'

--插入测试数据完毕

--开始取数据
select [范围],
sum(case [时间] when '201812' then [在职人数] end) as [在职人数],
sum(case [时间] when '201812' then [离职人数] end) as [离职人数],
max(case [时间] when '201812' then [时间] end) as [时间],
sum(case [时间] when '201901' then 在职人数 end) as [在职人数],
sum(case [时间] when '201901' then [离职人数] end) as [离职人数],
max(case [时间] when '201901' then [时间] end) as [时间],
sum(case [时间] when '201902' then 在职人数 end) as [在职人数],
sum(case [时间] when '201902' then [离职人数] end) as [离职人数],
max(case [时间] when '201902' then [时间] end) as [时间],
sum(case [时间] when '201903' then 在职人数 end) as [在职人数],
sum(case [时间] when '201903' then [离职人数] end) as [离职人数],
max(case [时间] when '201903' then [时间] end) as [时间],
sum(case [时间] when '201904' then 在职人数 end) as [在职人数],
sum(case [时间] when '201904' then [离职人数] end) as [离职人数],
max(case [时间] when '201904' then [时间] end) as [时间],
sum(case [时间] when '201905' then 在职人数 end) as [在职人数],
sum(case [时间] when '201905' then [离职人数] end) as [离职人数],
max(case [时间] when '201905' then [时间] end) as [时间],
sum(case [时间] when '201906' then 在职人数 end) as [在职人数],
sum(case [时间] when '201906' then [离职人数] end) as [离职人数],
max(case [时间] when '201906' then [时间] end) as [时间],
sum(case [时间] when '201907' then 在职人数 end) as [在职人数],
sum(case [时间] when '201907' then [离职人数] end) as [离职人数],
max(case [时间] when '201907' then [时间] end) as [时间],
sum(case [时间] when '201908' then 在职人数 end) as [在职人数],
sum(case [时间] when '201908' then [离职人数] end) as [离职人数],
max(case [时间] when '201908' then [时间] end) as [时间],
sum(case [时间] when '201909' then 在职人数 end) as [在职人数],
sum(case [时间] when '201909' then [离职人数] end) as [离职人数],
max(case [时间] when '201909' then [时间] end) as [时间],
sum(case [时间] when '201910' then 在职人数 end) as [在职人数],
sum(case [时间] when '201910' then [离职人数] end) as [离职人数],
max(case [时间] when '201910' then [时间] end) as [时间],
sum(case [时间] when '201911' then 在职人数 end) as [在职人数],
sum(case [时间] when '201911' then [离职人数] end) as [离职人数],
max(case [时间] when '201911' then [时间] end) as [时间],
sum(case [时间] when '201912' then 在职人数 end) as [在职人数],
sum(case [时间] when '201912' then [离职人数] end) as [离职人数],
max(case [时间] when '201912' then [时间] end) as [时间]
 from #T
 group by [范围]

 drop table #T

结果