按小时添加所有列的总和 [英] Add the sum of all the columns by hour

问题描述

我有两个表，GuestList和CustomerList。我加入了它们并使用动态SQL数据透视表将GuestList表中的city列转换为行或表标题，并在每个城市下显示平均人口。因此，在底部执行查询后，我的表看起来像这样，并且每个城市下都会显示平均人口。

时间| Atl_1 | Atl_2 | Atl_3 | NY_1 | NY_2 
 02/12 |  300  | 120 | 550 |  548  |  784  
 02/12 |  140  | 100 | 720 |  369  |  985  

我还可以选择在ASP.Net中汇总所有城市的人口。那么如何添加所有城市的总人口并显示如下？



注意：我的城市名称将是动态的。因此，用户可以从ASP.Net下拉列表选项中选择任何城市，因此总和也必须是动态的，而不是手动为每个城市添加，因为我们不知道用户将选择哪个城市。

时间| Atl_ALL | NY_ALL 
 02/12 | 970 | 1332 
 02/13 |  960  | 1354 

我的尝试：



以下是动态sql查询：

声明@ColumnNames nvarchar（max）= ' ' 
声明@SQL nvarchar（max）= < span class =code-string>' ' 
 
  select  @ColumnNames + = QUOTENAME（a.address）+ ' ，' 
 来自 GuestList   a 
 inner  join  CustomerList  as  b 
 on a.Id = b.Id 
  group  by a a .address 
由a.address命令
 
  set  @ColumnNames = left（@ColumnNames，LEN（@ColumnNames）-1） 
 
  set  @ SQL = N '  
 select Time，'  + @ColumnNames + '  
来自
（
选择a。来自GuestList的时间，a.city，a.population，b.Gender 
作为
内部联接CustomerList作为b 
 on a.Id = b.Id 
内部联接分裂（ @city，' ' ，' '  ）作为c 
在a.city = c.Data 
，其中a.city = c.Data 
）为SourceTable 
 pivot 
（avg（population）
 for city 
 in（' 
 + @ColumnNames + 
 ' ））作为数据透视表
按时间顺序' 
 
 
执行sp_executesql @SQL，
 N '  @ city nvarchar（max）' 
，@ c ity = @city 

解决方案

   -     - 首先将枢轴记录导出到##全局表（我使用#temp来显示目的） 
   -  -   我使用了String_split（）而不是你的Split（）函数..  
 
 创建 表 #TEMP（时间  varchar （ 50 ），Atl_1  int ，Atl_2  int ，Atl_3  int ，NY_1  int ，NY_2  int ）
  INSERT   INTO  #TEMP 
  VALUES  
（'  02/12'， 300 ， 120 ，< span class =code-digit> 550 ， 548 ， 784 ）
，（'  02/13'， 140 ， 100 ， 720 ， 369  ， 985 ）; 
 
  DECLARE   @ query   nvarchar （max）， @ list   varchar （max）= '  Atl_1，Atl_2，Atl_3，NY_1，NY_2'; 
 
  WITH  CTE  AS （
 选择  DISTINCT  
东西（（ select   DISTINCT  '  + SUM（' + value +  ）' 来自 string_split（ @ list ，' ，'） as  dd 其中​​ dd.value 喜欢 ' ％' + substring（ss.value， 1 ，charindex（'  _'，ss.value））+ '   xml路径<％class   ' '））， 1 ， 1 ，' '）+ '  AS ' + substring（ss.value， 1 ，charindex（'  _'，ss.value））+ '  ALL' < span class =code-keyword> as  pcols  FROM  string_split（ @ list ，< span class =code-string>' ，'） as  ss 
）
  SELECT  @ list = Stuff（（ select  ' ，' + Pcols 来自 CTE   XML路径（' '））， 1 ， 1 ，' '）
 
  SET  @ query = N ' 选择时间，' + @ list +  来自#TEMP GROUP BY time'; 
 
  EXEC （ @ query ）; 

I have two tables, GuestList and CustomerList. I joined them and used dynamic SQL pivot table to convert the 'city' column from GuestList table into rows or table headers and the average population would be displayed under each city. So after executing the query at the bottom, my table looks like this and the average population is displayed under each city.

Time   | Atl_1  | Atl_2  | Atl_3 | NY_1 | NY_2
02/12  | 300    |120     |550    | 548  | 784
02/12  | 140    |100     |720    | 369  | 985

I also have an option to sum all the cities' population in ASP.Net. So how do I add all total population of the cities and show like this?

NOTE: My cities name will be dynamic. So the user can select any city from ASP.Net drop down list option so the sum also has to be dynamic instead of adding manually for each city since we don't know which city the user is going to choose.

Time   | Atl_ALL  | NY_ALL  
02/12  |970      |1332    
02/13  | 960      |1354

What I have tried:

Below is the dynamic sql query:

declare @ColumnNames nvarchar(max) = ''
declare @SQL nvarchar(max) = ''

select @ColumnNames += QUOTENAME(a.address) + ','
from GuestList as a
inner join CustomerList as b
on a.Id = b.Id
group by a.address
order by a.address 

set @ColumnNames = left(@ColumnNames, LEN(@ColumnNames)-1  )

set @SQL= N'
select Time,' + @ColumnNames + '
from 
(
select a.Time, a.city, a.population, b.Gender
from GuestList as a
inner join CustomerList as b
on a.Id = b.Id
inner join Split(@city, '','') as c
on a.city = c.Data
where a.city = c.Data
) as SourceTable
pivot
(avg(population) 
for city 
in (' 
    + @ColumnNames + 
    ')) as PivotTable
order by Time'


execute sp_executesql @SQL,
                        N'@city nvarchar(max)'
                        ,@city = @city

解决方案

---first export pivot records into ## global table(i used #temp for showing purpose)
--i have used String_split() instead of your Split() function.. 

CREATE TABLE #TEMP(Time varchar(50),Atl_1  int, Atl_2  int, Atl_3 int, NY_1 int, NY_2 int)
INSERT INTO #TEMP
VALUES
('02/12'  , 300,120 ,550, 548, 784)
,('02/13'  , 140,100 ,720, 369, 985);

DECLARE @query nvarchar(max),@list varchar(max)='Atl_1,Atl_2,Atl_3,NY_1,NY_2';

WITH CTE AS(
  select DISTINCT 
        Stuff((select DISTINCT '+SUM('+value+')' from string_split(@list,',') as dd where dd.value like '%'+substring(ss.value,1,charindex('_',ss.value))+'%'  for xml path('')),1,1,'')+' AS '+substring(ss.value,1,charindex('_',ss.value))+'ALL' as  pcols FROM   string_split(@list,',') as ss
           )
SELECT @list=Stuff((select ','+Pcols from CTE for XML Path('')),1,1,'')

SET @query=N'select time,'+@list+' from #TEMP GROUP BY time';

EXEC(@query);

这篇关于按小时添加所有列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！