代码之家 › 专栏 › 技术社区 › JonWay

如何动态添加列?

sql-server-2008 tsql sql-server

JonWay · 技术社区 · 6 年前

我有以下数据用于计算每日事件。数字列每天都在变化,例如第一天可以是1到5,第二天可以是1到8,第三天可以是1到10等等。因此,我使用动态透视来获取列,并将其插入临时表##NO中。从表中,我想将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等等

1 回复 | 直到 6 年前

Sean Lange 6 年前

您可以使用它动态地获得总和。

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