如何在sql查询中选择24hours作为字段名称水平(0:00,01:00,02:00,03:00 ....... 24:00) [英] How select 24hours as a fields name horizontal like(0:00,01:00,02:00,03:00.......24:00) in sql query
本文介绍了如何在sql查询中选择24hours作为字段名称水平(0:00,01:00,02:00,03:00 ....... 24:00)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何选择24小时作为字段名称水平(0:00,01:00,02:00) ,03:00 ....... 24:00)在sql查询中
CAST(DATEPART(HOUR,dateadd( S,Submit_Date + ' 19800',' 19700101')) as varchar )+ ' :00' as ' 小时'
解决方案
< blockquote>你可以使用PIVOT - 看看这篇CP文章 SQL Wizardry第七部分 - PIVOT和任意数据列表 [ ^ ]
我无法让你的查询工作,所以这是一个简化版本,只是为了演示
选择 * 来自
(
选择 CAST(Submit_Date AS DATE ) as colDate,' Hour' + CAST(DATEPART(HOUR,Submit_Date) as varchar ) as colHour,
char_Circle = ' andhar pradesh',
count( 1 ) as aCount
来自 HPD_Help_Desk
group by CAST(Submit_Date AS DATE ),DATEPART(HOUR,Submit_Date)
) as sourcedata
PIVOT
(
SUM(aCount) FOR colHour in ([Hour 0 ],[Hour 1 ],[小时 2 ],[小时 3 ],[小时 4 ],[小时 5 ],[小时 6 ],[小时 7 ],[小时 8 ], [小时 9 ],[小时 11 ],[小时 12 ])
) as pivotdata
Hi,
How select 24hours as a fields name horizontal like(0:00,01:00,02:00,03:00.......24:00) in sql query
CAST(DATEPART(HOUR, dateadd(S,Submit_Date+'19800','19700101')) as varchar)+ ':00' as 'Hour'
解决方案
You can use PIVOT - have a look at this CP article SQL Wizardry Part Seven - PIVOT and arbitrary lists of data[^]
I couldn't get your query to work, so here is a simplified version just to demonstrateselect * from ( select CAST(Submit_Date AS DATE) as colDate, 'Hour ' + CAST(DATEPART(HOUR, Submit_Date) as varchar) as colHour, char_Circle='andhar pradesh' , count(1) as aCount from HPD_Help_Desk group by CAST(Submit_Date AS DATE),DATEPART(HOUR, Submit_Date) ) as sourcedata PIVOT ( SUM(aCount) FOR colHour in ([Hour 0],[Hour 1],[Hour 2],[Hour 3],[Hour 4],[Hour 5],[Hour 6],[Hour 7],[Hour 8],[Hour 9],[Hour 11],[Hour 12]) ) as pivotdata
这篇关于如何在sql查询中选择24hours作为字段名称水平(0:00,01:00,02:00,03:00 ....... 24:00)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文