SQL中遇到的实例

1.强制字符转换拼接字段

.select 'TOP'+CAST(ROW_NUMBER() OVER (ORDER BY [业务量] DESC) as VARCHAR(20)) as 热门事项 ,t.事项名称 as 事项名称,t.业务量 as 业务量 from (select top 5 [JITEMNAME] as 事项名称,count([JITEMNAME]) as 业务量 from [X行政审批_P票号明细] group by [JITEMNAME] order by 业务量 desc)t
ROW_NUMBER() OVER (ORDER BY [业务量] DESC
是新增加一个排序字段
‘TOP’+CAST(ROW_NUMBER() OVER (ORDER BY [业务量] DESC)
强制字符转换

2.int 时间（秒）转换为度分秒

.CONVERT(VARCHAR(10),([avgDealTime])/3600)+':'+CONVERT(VARCHAR(10),([avgDealTime])%3600/60)+':'+CONVERT(VARCHAR(10),([avgDealTime])%3600%60)+''
int 时间（秒）转换为度分秒

3.右侧补零

select '事项总量' as name , CONVERT(VARCHAR(10),[finTicketCount]) as value from [X行政审批_S实时办理] union all select '办理平均用时' as name,CONVERT(VARCHAR(10),([avgDealTime])/3600)+':'+CONVERT(VARCHAR(10),RIGHT('00'+CAST( (([avgDealTime])%3600/60) AS nvarchar(10)),2)+':'+ CONVERT(VARCHAR(10),RIGHT('00'+CAST( ((([avgDealTime])%3600%60)) AS nvarchar(10)),2))+'' )as value from [X行政审批_S实时办理]
RIGHT(‘00’+CAST(ID AS nvarchar(10)),2)
右侧补零
得出的是3分6秒，但是想要变成03分06秒，就用这补零

4.动态行列转置

原来的表单如下

所要达到的效果如下：

Declare @Actions nvarchar(200), @Sql nvarchar(4000)
select @Actions = LEFT(活动属性名称,LEN(活动属性名称)-1)
from (  
	select(
		SELECT '['+活动属性名称+'],' FROM (
			select 活动属性名称 from 
			(
			SELECT top 5 [活动属性名称],count(*)