求一sql语句,行列转换

gbys 2018-01-03 10:07:54
A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12 A13 A14 a15 a16 a17
8 5 7 8 10 6 11 9 9 6 5 11 12 8 6 12 5 6

想把上面的结果变成下面的,有排序:
A12 12
a15 12
A6 11
A11 11
A4 10
A7 9
A8 9
A0 8
A3 8
A13 8
A2 7
A5 6
A9 6
A14 6
a17 6
A1 5
A10 5
a16 5
...全文
240 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
gbys 2018-01-03
  • 打赏
  • 举报
回复
谢谢
听雨停了 2018-01-03
  • 打赏
  • 举报
回复
引用 2 楼 gbys 的回复:
@听雨停了 A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12 A13 A14 a15 a16 a17 8 5 7 8 10 6 11 9 9 6 5 11 12 8 6 12 5 6 这一行数据是从下面的语句得来的;这样的话应该怎么写? 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 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
你这个应该是行转列吧 你把上面查询出来的数据插入到一个表中,比如我上面的tab表,然后用上面的列转行就可以得到你要的结果的

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)
gbys 2018-01-03
  • 打赏
  • 举报
回复
@听雨停了 A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12 A13 A14 a15 a16 a17 8 5 7 8 10 6 11 9 9 6 5 11 12 8 6 12 5 6 这一行数据是从下面的语句得来的;这样的话应该怎么写? 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 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
听雨停了 2018-01-03
  • 打赏
  • 举报
回复

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)

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧