时间戳

首页 > 解决方案 > 循环创建新列

sql - 循环创建新列

问题描述

假设我有一个包含 4 列MonthEnd、Gender和Weight的SQL Server 表Age。

现在，MonthEnd从 201901 到 201912。

我有一个过程，其中运行一个生成各种指标的查询，例如每个月的总和（体重）和平均（年龄）。

现在，我where MonthEnd = 201901在为该月创建指标时设置了一个。

输出是一个有 2 列和 2 行的表。Column1 标题是 Metric，Column2 标题是 201901，然后 row1 column1 表示 Total_Weight，row1 column2 是 sum(weight) 函数生成的值。

类似地，row2 column1 表示 Average_Age，row2 column2 是 Average(Age) 函数的结果。

我已经完成了上述工作。

然后，我必须在将 where 子句更改为 where MonthEnd = 201902 后重新运行代码，然后将相同的结构应用于结果。

我希望能够一步运行 201901 到 201912 并让输出反映 13 列，即 column1 仍然是具有标题“Metric”的度量，但接下来的 12 列将具有标题 201901 到 201912 以及相应的结果包含在标题下方的每个月。

T-SQL 可以做到这一点吗？我觉得我可以使用 Pivot 使用 1 个聚合函数来做到这一点，但我还没有弄清楚如何同时执行多个聚合函数。

-- THIS SECTION SETS UP THE DATA SET STARTING POINT ---------

IF OBJECT_ID('[dbo].[example]') IS NOT NULL DROP TABLE [dbo].[example]
GO

CREATE TABLE [dbo].[example]
(
MonthEnd INT NOT NULL,
Grade VARCHAR(50) NOT NULL,
Name VARCHAR(50) NOT NULL,
Amount INT NULL
)
GO

INSERT INTO [dbo].[example]
VALUES 
(201901, 'A', 'Josh', 100),
(201901, 'A', 'Joe', 50),
(201901, 'A', 'Jill', 150),
(201901, 'B', 'Julie', 150),
(201901, 'B', 'Jim', 350),
(201901, 'C', 'Jeff', 100),
(201901, 'C', 'Jack', 125),
(201901, 'C', 'Jillian', 150),
(201901, 'C', 'Jess', 175),
(201901, 'C', 'James', 450),
(201902, 'A', 'Josh', 95),
(201902, 'A', 'Jill', 105),
(201902, 'B', 'Julie', 125),
(201902, 'B', 'Jim', 325),
(201902, 'C', 'Jeff', 75),
(201902, 'C', 'Jack', 100),
(201902, 'C', 'Jillian', 125),
(201903, 'A', 'Josh', 50),
(201903, 'B', 'Julie', 75),
(201903, 'B', 'Jim', 300),
(201903, 'C', 'Jeff', 50),
(201903, 'C', 'Jack', 50);

select * from [dbo].[example]

-- THE BELOW SECTION GENERATES THE DESIRED RESULTS WHICH I'D LIKE TO ACHIEVE THROUGH A PIVOT OR LOOP ---
-- I HAVE MONTHS FROM 201801 through 202103, SO I'D LIKE TO ITERATE THROUGH THEM AND PRODUCE THE DESIRED RESULT SET PROGRAMMATICALLY ---

IF OBJECT_ID('tempdb..#temp1') IS NOT NULL drop table #temp1
IF OBJECT_ID('tempdb..#temp2') IS NOT NULL drop table #temp2
IF OBJECT_ID('tempdb..#temp3') IS NOT NULL drop table #temp3

select 
    cast(Grade as varchar(max)) as Grade
    ,cast(Name as varchar(max)) as Name
    ,'SUM' as Metric
    ,cast(sum(Amount) as varchar(max)) as '201901'
into #temp1
from [dbo].[example] 
where MonthEnd = '201901'
group by 
    cast(Grade as varchar(max))
    ,cast(Name as varchar(max))

    union all

select 
    cast(Grade as varchar(max)) as Grade
    ,cast(Name as varchar(max)) as Name
    ,'AVG' as Metric
    ,cast(avg(Amount) as varchar(max)) as '201901'
from [dbo].[example] 
where MonthEnd = '201901'
group by 
    cast(Grade as varchar(max))
    ,cast(Name as varchar(max))


select 
    cast(Grade as varchar(max)) as Grade
    ,cast(Name as varchar(max)) as Name
    ,'SUM' as Metric
    ,cast(sum(Amount) as varchar(max)) as '201902'
into #temp2
from [dbo].[example] 
where MonthEnd = '201902'
group by 
    cast(Grade as varchar(max))
    ,cast(Name as varchar(max))

    union all

select 
    cast(Grade as varchar(max)) as Grade
    ,cast(Name as varchar(max)) as Name
    ,'AVG' as Metric
    ,cast(avg(Amount) as varchar(max)) as '201902'
from [dbo].[example] 
where MonthEnd = '201902'
group by 
    cast(Grade as varchar(max))
    ,cast(Name as varchar(max))


select 
    cast(Grade as varchar(max)) as Grade
    ,cast(Name as varchar(max)) as Name
    ,'SUM' as Metric
    ,cast(sum(Amount) as varchar(max)) as '201903'
into #temp3
from [dbo].[example] 
where MonthEnd = '201903'
group by 
    cast(Grade as varchar(max))
    ,cast(Name as varchar(max))

    union all

select 
    cast(Grade as varchar(max)) as Grade
    ,cast(Name as varchar(max)) as Name
    ,'AVG' as Metric
    ,cast(avg(Amount) as varchar(max)) as '201903'
from [dbo].[example] 
where MonthEnd = '201903'
group by 
    cast(Grade as varchar(max))
    ,cast(Name as varchar(max))

select 
    a.Grade
    ,a.Name
    ,a.Metric
    ,a.[201901]
    ,b.[201902]
    ,c.[201903]
from #temp1 a
left join #temp2 b
on  a.Grade = b.Grade and a.Name = b.Name and a.Metric = b.Metric
left join #temp3 c
on  a.Grade = c.Grade and a.Name = c.Name and a.Metric = c.Metric

标签： sqlsql-servertsql

解决方案

我能够使用枢轴方法来生成所需的结果

推荐阅读