With CR as (select addtime,[edit name 1],[edit name 2],[edit name 3]from (select Newsid,addtime,auditinguser from News) as a Pivot ( COUNT (A.NEWSID) for A.auditinguser in ([Edit name 1],[edit name 2],[edit Name 3])) as Pvtwhere addtime between ' 2014-11-24 00:00:00 ' and ' 20 14-11-30 23:59:59 ' GROUP by addtime,[edit name 1],[edit name 2],[edit Name 3]) SELECT CONVERT (NVARCHAR (), addtime,120) as ' time ', [edit name 1], [Edit name 2],[edit Name 3] from crunion (SELECT ' total ' as addtime, sum (cr.[edit name 1]) as ' Edit Name 1 ', SUM (cr.[edit Name 2]) as [edit name 2],sum (cr.[edit Name 3 ]) as [edit name 3] from CR)
Before execution, the target database should be set to sql2005 above the compatibility, otherwise it will be error.
The results obtained:
Time Edit name 1 Edit Name 2 edit name 32014-11-24 08:36:031002014-11-24 09:42:150012014-11-24 09:42:310102014-11-24 09:43:25100201 4-11-24 09:45:540012014-11-24 10:22:130012014-11-24 10:31:170012014-11-24 10:39:360012014-11-24 11:27:120102014-11-24 11:28:570002014-11-24 14:28:570102014-11-24 14:31:051002014-11-24 14:47:340102014-11-24 15:35:540102014-11-24 15:37:251002014-11-24 15:41:141002014-11-24 15:49:390102014-11-24 15:56:300102014-11-24 16:02:101002014-11-24 16:17:320012014-11-24 16:26:501002014-11-24 16:34:430102014-11-24 16:41:450002014-11-24 16:47:420002014-11-24 17:01:270002014-11-24 17:17:160012014-11-24 20:14:350002014-11-25 08:44:350102014-11-25 09:18:060102014-11-25 09:23:491002014-11-25 10:37:550102014-11-25 10:50:530012014-11-25 11:02:300012014-11-25 11:28:110102014-11-25 14:28:240102014-11-25 14:35:091002014-11-25 14:44:430102014-11-25 15:10:520012014-11-25 15:14:030102014-11-25 15:28:070012014-11-25 15:34:240012014-11-25 15:35:080012014-11-25 15:40:510012014-11-25 16:06:521002014-11-25 16:07:400012014-11-25 16:30:130012014-11-25 16:44:331002014-11-25 16:53:331002014-11-25 17:06:541002014-11-25 17:12:051002014-11-25 17:17:200102014-11-26 08:56:241002014-11-26 09:01:380102014-11-26 09:09:530102014-11-26 09:22:491002014-11-26 09:31:260102014-11-26 10:01:380012014-11-26 10:34:351002014-11-26 10:42:530012014-11-26 10:45:150102014-11-26 11:07:300012014-11-26 11:26:550102014-11-26 11:28:440012014-11-26 14:30:210102014-11-26 14:50:070102014-11-26 15:07:400102014-11-26 15:23:501002014-11-26 15:35:241002014-11-26 15:40:411002014-11-26 15:46:220102014-11-26 16:15:420012014-11-26 16:27:360102014-11-26 16:38:381002014-11-26 16:39:580012014-11-26 16:42:170012014-11-26 16:45:110012014-11-26 16:51:140012014-11-27 08:41:460102014-11-27 08:50:381002014-11-27 09:41:120102014-11-27 09:50:591002014-11-27 09:51:320102014-11-27 10:36:091002014-11-27 10:54:570012014-11-27 14:16:581002014-11-27 14:45:370102014-11-27 14:50:220012014-11-27 14:52:130012014-11-27 14:55:290102014-11-2715:03:230012014-11-27 15:07:490012014-11-27 15:19:070012014-11-27 15:38:140102014-11-27 15:50:250102014-11-27 15:58:041002014-11-27 15:58:480012014-11-27 16:08:590012014-11-27 16:14:530102014-11-27 16:17:100012014-11-27 16:19:591002014-11-27 16:41:571002014-11-28 08:46:401002014-11-28 09:03:070102014-11-28 09:27:110102014-11-28 09:44:440012014-11-28 09:48:480012014-11-28 09:53:520102014-11-28 10:34:090012014-11-28 10:39:200102014-11-28 10:51:161002014-11-28 11:38:251002014-11-28 11:44:271002014-11-28 14:36:291002014-11-28 15:03:200012014-11-28 15:34:430012014-11-28 15:40:291002014-11-28 15:40:420012014-11-28 15:50:401002014-11-28 16:07:171002014-11-28 16:09:410102014-11-28 16:10:120012014-11-28 16:27:120012014-11-28 17:02:380012014-11-28 21:24:180002014-11-29 08:31:360002014-11-29 12:32:320012014-11-29 12:35:140012014-11-29 12:44:430012014-11-29 13:13:160012014-11-29 13:20:210012014-11-29 13:30:120012014-11-29 13:42:310012014-11-29 20:30:360002014-11-29 20:50:030002014-11-29 21:12:300012014-11-30 12:12:080012014-11-30 12:30:360012014-11-30 12:33:360012014-11-30 12:37:540012014-11-30 12:43:380012014-11-30 12:52:180012014-11-30 12:56:470012014-11-30 17:55:540012014-11-30 17:55:550012014-11-30 18:30:410012014-11-30 18:48:150012014-11-30 18:54:550012014-11-30 18:56:590012014-11-30 19:15:300012014-11-30 19:29:41001 Total 363966
Use with AS and pivot in MSSQL to compile press release time details and totals