34,575
社区成员
发帖
与我相关
我的任务
分享
if not object_id(N'tab') is null
drop table tab
Go
SELECT sum(cast(a.A0 as int)) A0,sum(cast(a.A1 as int)) A1,sum(cast(a.A2 as int)) A2,
sum(cast(a.A3 as int)) A3,sum(cast(a.A4 as int)) A4,sum(cast(a.A5 as int)) A5,
sum(cast(a.A6 as int)) A6,sum(cast(a.A7 as int)) A7,sum(cast(a.A8 as int)) A8,
sum(cast(a.A9 as int)) A9,sum(cast(a.A10 as int)) A10,sum(cast(a.A11 as int)) A11,
sum(cast(a.A12 as int)) A12,sum(cast(a.A13 as int)) A13,sum(cast(a.A14 as int)) A14,
sum(cast(a.A15 as int)) a15,sum(cast(a.A16 as int)) a16,sum(cast(a.A17 as int)) a17
INTO tab --插入tab表中
FROM dbo.BUS_HR_MXYG AS a INNER JOIN dbo.BPMInstTasks AS b
ON a.TASKID = b.TaskID
WHERE (b.State = 'Approved ')
AND a.year=2018
group by year
--列转行显示
declare @sql nvarchar(4000)
select @sql=isnull(@sql+',','')+quotename(Name)
from syscolumns
where ID=object_id('tab')
order by Colid
set @sql='select [title],[cnt] from tab unpivot ([cnt] for [title] in('+@sql+'))b order by cnt desc,title '
exec(@sql)
use Tempdb
go
--> --> 听雨停了-->生成测试数据
if not object_id(N'tab') is null
drop table tab
Go
Create table tab([A0] int,[A1] int,[A2] int,[A3] int,[A4] int,[A5] int,[A6] int,[A7] int,[A8] int,[A9] int,[A10] int,[A11] int,[A12] int,[A13] int,[A14] int,[a15] int,[a16] int,[a17] int)
Insert tab
select 8,5,7,8,10,6,11,9,9,6,5,11,12,8,6,12,5,6
Go
--列转行显示
declare @sql nvarchar(4000)
select @sql=isnull(@sql+',','')+quotename(Name)
from syscolumns
where ID=object_id('tab')
order by Colid
set @sql='select [title],[cnt] from tab unpivot ([cnt] for [title] in('+@sql+'))b order by cnt desc'
exec(@sql)