sql动态行转列的两种方法
时间:2024-01-24 07:17:41
第一种方法:
select *from ( select Url,case when Month=01 then '1月' when Month=02 then '2月' when Month=03 then '3月' when Month=04 then '4月' when Month=05 then '5月' when Month=06 then '6月' when Month=07 then '7月' when Month=08 then '8月' when Month=09 then '9月' when Month=10 then ' 10月' when Month=11 then '11月' when Month=12 then ' 12月'
end month,Quality from (
select Url,DATENAME(M,AuditingTime)Month,SUM(Quality) Quality from tb_order as a left join tb_WebSiteInfo as b on a.WebSiteInfoID=b.ID left join tb_OrderList as c on c.OrderID=a.ID where AuditingTime>'2013-01-01' and b.ID>0 and Auditing=2
group by Url,DATENAME(M,AuditingTime) )as h ) as hh
pivot ( sum(Quality) for month in([1月],[2月],[3月],[4月],[5月],[6月],[7月],[8月],[9月],[10月],[11月],[12月])) as a
第二种方法:
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + '['+CONVERT(varchar(7),AuditingTime,20)+']'
from tb_order as a left join tb_WebSiteInfo as b on a.WebSiteInfoID=b.ID left join tb_OrderList as c on c.OrderID=a.ID where AuditingTime>'2013-01-01' and b.ID>0 and Auditing=2
group by CONVERT(varchar(7),AuditingTime,20) print @sql declare @sql2 varchar(8000)='' set @sql2=' select *from (
select Url, CONVERT(varchar(7),AuditingTime,20) AuditingTime,SUM(Quality) Quality from tb_order as a left join tb_WebSiteInfo as b on a.WebSiteInfoID=b.ID left join tb_OrderList as c on c.OrderID=a.ID where b.ID>0 and Auditing=2
group by Url, CONVERT(varchar(7),AuditingTime,20)
) as hh pivot (sum(Quality) for AuditingTime in (' + @sql + ')) b'
print @sql2
exec(@sql2)
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
tornado框架blog模块分析与使用
Python实现Tracert追踪TTL值的方法详解
![](https://img.aspxhome.com/file/2023/5/66405_0s.png)
python3 拼接字符串的7种方法
webpack-dev-server自动更新页面方法
![](https://img.aspxhome.com/file/2023/9/136159_0s.jpg)
Django学习笔记之ORM基础教程
![](https://img.aspxhome.com/file/2023/2/78722_0s.png)
Python实现html转换为pdf报告(生成pdf报告)功能示例
pandas按行按列遍历Dataframe的几种方式
![](https://img.aspxhome.com/file/2023/1/131391_0s.png)
mysql中general_log日志知识点介绍
appium测试之APP元素定位及基本工具介绍
![](https://img.aspxhome.com/file/2023/6/104986_0s.png)
jupyter notebook 多环境conda kernel配置方式
![](https://img.aspxhome.com/file/2023/1/133721_0s.jpg)
浅谈python中的错误与异常
利用Python编写个冷笑话生成器
![](https://img.aspxhome.com/file/2023/1/112521_0s.jpg)
解析SQL Server 2008中的新语句:MERGE
引起用户注意的界面方式
![](https://img.aspxhome.com/file/UploadPic/200710/7/2007107212329681s.jpg)
golang容易导致内存泄漏的6种情况汇总
python global关键字的用法详解
Keras保存模型并载入模型继续训练的实现
![](https://img.aspxhome.com/file/2023/8/75958_0s.png)