sql-server - 如何动态添加列?
问题描述
我有以下用于计算每日事件的数据。数字列每天都在变化,例如第一天可以是 1 到 5,第二天可以是 1 到 8,第三天可以是 1 到 10 等等。因此,我使用动态透视来获取列并将其插入到临时表中 # #不。从表中我想将 1 到第 N 列的值相加以获得总计。
数据
CREATE TABLE ##TBL (Number INT, Months VARCHAR(10), Total INT)
INSERT INTO ##TBL VALUES
(3,'Dec',1),(10,'Dec',1),(8,'Dec',1),(6,'Mar',1),(9,'Mar',1),(6,'Mar',1),(3,'Dec',1),(5,'Mar',1),(3,'Mar',1),
(2,'Mar',1),(10,'Dec',1),(7,'Mar',1),(3,'Mar',1),(6,'Dec',1),(4,'Mar',1),(9,'Dec',1),(1,'Mar',1),(3,'Mar',1),
(5,'Dec',1),(9,'Dec',1),(5,'Mar',1),(8,'Mar',1),(7,'Mar',1),(5,'Mar',1),(4,'Mar',1),(8,'Mar',1),(3,'Mar',1),
(7,'Mar',1),(5,'Mar',1),(2,'Mar',1),(6,'Mar',1),(2,'Mar',1),(8,'Dec',1),(1,'Mar',1),(5,'Mar',1),(6,'Mar',1),
(8,'Mar',1),(3,'Mar',1),(9,'Dec',1),(5,'Dec',1),(8,'Dec',1),(7,'Dec',1),
(5,'Dec',1)
产生输出的数据透视表
DECLARE @Numb AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @Numb =(SELECT SUBSTRING(
(
SELECT distinct', ' + QUOTENAME(Number) AS 'data()'
FROM ##TBL
FOR XML PATH('')
), 2 , 9999) As nums)
SET @query =
'SELECT *
INTO ##NO
FROM ##TBL
PIVOT
( SUM(Total) FOR Number IN ('+@Numb+')) PV'
EXEC (@query)
SELECT
*,
ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)+ISNULL([5],0)+ISNULL([6],0)+ISNULL([7],0)+
ISNULL([8],0)+ISNULL([9],0)+ISNULL([10],0) AS Grand_Total
FROM ##NO
DROP TABLE ##NO
当前输出
Months 1 10 2 3 4 5 6 7 8 9 Grand_Total
Dec NULL 2 NULL 2 NULL 3 1 1 3 3 15
Mar 2 NULL 3 5 2 5 4 3 3 1 28
有没有一种方法可以在列最后减少或增加时对列进行动态求和?列总是从 1,2,3….n 开始,我并不关心列的顺序
第 1 天所需的输出
Months 1 2 3 4 5 Grand_Total
Dec NULL NULL 2 NULL 3 5
Mar 2 3 5 2 5 17
第 2 天所需的输出
Months 1 2 3 Grand_Total
Dec NULL NULL 2 2
Mar 2 3 5 10
我想要的是数字列数据何时增加或减少。我希望能够将它们加起来。有时数字从 1 开始到 5 结束,另一个时间从 1 到 7 另一个时间从 1 到 8 等等
解决方案
您可以使用它来动态获取总和。
declare @SUM nvarchar(max) = ''
SELECT @SUM = @SUM + 'ISNULL(' + QUOTENAME(Number) + ',0)+'
FROM ##TBL
group by Number
select @SUM = left(@SUM, len(@SUM) - 1)
然后将其添加到您的查询中非常简单。
SET @query =
'SELECT *, Grand_Total = ' + @SUM
+ 'INTO ##NO
FROM ##TBL
PIVOT
( SUM(Total) FOR Number IN ('+@Numb+')) PV'
所以功能齐全的代码看起来像这样。
CREATE TABLE ##TBL (Number INT, Months VARCHAR(10), Total INT)
INSERT INTO ##TBL VALUES
(3,'Dec',1),(10,'Dec',1),(8,'Dec',1),(6,'Mar',1),(9,'Mar',1),(6,'Mar',1),(3,'Dec',1),(5,'Mar',1),(3,'Mar',1),
(2,'Mar',1),(10,'Dec',1),(7,'Mar',1),(3,'Mar',1),(6,'Dec',1),(4,'Mar',1),(9,'Dec',1),(1,'Mar',1),(3,'Mar',1),
(5,'Dec',1),(9,'Dec',1),(5,'Mar',1),(8,'Mar',1),(7,'Mar',1),(5,'Mar',1),(4,'Mar',1),(8,'Mar',1),(3,'Mar',1),
(7,'Mar',1),(5,'Mar',1),(2,'Mar',1),(6,'Mar',1),(2,'Mar',1),(8,'Dec',1),(1,'Mar',1),(5,'Mar',1),(6,'Mar',1),
(8,'Mar',1),(3,'Mar',1),(9,'Dec',1),(5,'Dec',1),(8,'Dec',1),(7,'Dec',1),
(5,'Dec',1)
DECLARE @Numb AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @Numb =(SELECT SUBSTRING(
(
SELECT distinct', ' + QUOTENAME(Number) AS 'data()'
FROM ##TBL
FOR XML PATH('')
), 2 , 9999) As nums)
declare @SUM nvarchar(max) = ''
SELECT @SUM = @SUM + 'ISNULL(' + QUOTENAME(Number) + ',0)+'
FROM ##TBL
group by Number
select @SUM = left(@SUM, len(@SUM) - 1)
SET @query =
'SELECT *, Grand_Total = ' + @SUM
+ 'INTO ##NO
FROM ##TBL
PIVOT
( SUM(Total) FOR Number IN ('+@Numb+')) PV'
select @query
EXEC (@query)
select * from ##NO
DROP TABLE ##NO
drop table ##TBL
推荐阅读
- amazon-web-services - 列出 cloudwatch-logs 关系时出错
- powershell - Powershell - 哈希表更新键值
- c# - 一对多关系.net mvc的错误保存
- javascript - 如果没有“新”错误,则无法调用类构造函数 dataLayer
- rstudio-server - 如何列出暂停的 rstudio 服务器会话?
- reactjs - 如何从 fetch 请求中的 readableStream 响应中获取可下载文件
- javascript - 如果 AJAX REST API 成功或失败,我如何访问成功或错误回调中的请求标头?
- php - 在 PHP 中连接到没有 SSL 的远程服务器之前,将 tcp 套接字绑定到特定的 src 端口
- swift - 如何从文件夹文件中创建一个字符串数组
- c# - Controller 中 Create 方法的默认值