如何使用动态SQL添加2列的值 [英] How to use dynamic SQL to add value of 2 columns
问题描述
我有一张小桌子,上面放着学生标记.表格数据如下图所示.
I have small table which contains students marks. Table data is shown in below image.
它看起来像下面的excel
It is look like below in excel
我想使用动态SQL计算总数.我不想更新它.但是,我只想使用动态SQL选择所有具有计算得出的总数的数据.
I want to calculate the total using dynamic SQL. I don't want to update it. However, I just want to select all the data with calculated total using dynamic SQL.
请参考以下代码:
DECLARE @SQL NVARCHAR(MAX)=''
DECLARE @SNumberList NVARCHAR(MAX)=''
DECLARE @CalculatedLineNumbers NVARCHAR(MAX)=''
SELECT @CalculatedLineNumbers = @CalculatedLineNumbers+ ', '+
CASE WHEN SNo = 7 THEN '[1] + [4] [7]'
WHEN SNo = 8 THEN '[2] + [5] [8]'
WHEN SNo = 9 THEN '[3] + [6] [7]'
ELSE QUOTENAME(SNo)
END
FROM Student
SELECT @SNumberList = @SNumberList+ ', '+QUOTENAME(SNo)
FROM Student
SELECT @SNumberList=STUFF(@SNumberList, 1,1, ''),
@CalculatedLineNumbers=STUFF(@CalculatedLineNumbers,1,1,'')
SET @SQL= '
SELECT Year,'+@CalculatedLineNumbers+'
FROM
(
SELECT *
from Student s) AS J
PIVOT
(
MAX([Marks]) FOR Marks IN ('+@SNumberList+')
) AS P'
EXEC SP_EXECUTESQL @SQL
推荐答案
以excel截图作为预期的输出,您只需指定感兴趣的Year
即可完成此操作.
Taking the excel screenshot to be the expected output, you could accomplish this with just specifying the Year
of interest.
样本数据:
create table #sample_data
(
SNo int
, [LineNo] int
, ColumnNo int
, LineName varchar(15)
, ColumnName varchar(25)
, Marks int
, [Year] int
)
insert into #sample_data
values (1, 1, 1, 'Math', 'Jay', 97, 2018)
, (2, 1, 2, 'Math', 'Sam', 95, 2018)
, (3, 1, 3, 'Math', 'Jack', 90, 2018)
, (4, 2, 1, 'Science', 'Jay', 87, 2018)
, (5, 2, 2, 'Science', 'Sam', 88, 2018)
, (6, 2, 3, 'Science', 'Jack', 86, 2018)
, (7, 3, 1, 'Total', 'Jay', null, 2018)
, (8, 3, 2, 'Total', 'Sam', null, 2018)
, (9, 3, 3, 'Total', 'Jack', null, 2018)
答案:
下面的脚本根据设置Year
来确定相关的ColumnName
值,并基于ColumnNo
值强制列以预期的顺序显示.调整适当的记录后,查询将使用 group by grouping sets
生成Total
记录.
The script below, determines the relevant ColumnName
values based on setting the Year
, and forces the columns to show up in the expected order based on the ColumnNo
values. After pivoting the appropriate records, the query makes use of the group by grouping sets
to generate the Total
record.
declare @ColumnNameList nvarchar(max)
, @ColumnNameListSums nvarchar(max)
, @DynamicQuery nvarchar(max)
, @Year int = 2018 --set by OP in question
--get the full list of ColumnNames in a delimeter ("|") seperated string
set @ColumnNameList =
(
select stuff((
select '| ' + a.ColumnName
from (
select t.ColumnName
, min(t.ColumnNo) as ColumnNo
from #sample_data as t
where t.[Year] = @Year
group by t.ColumnName
) as a
order by a.ColumnNo
for xml path ('')
),1,1,'')
);
--its possible to use the previous variable as well, but easier to create another one
set @ColumnNameListSums =
(
select stuff((
select ', sum(a.' + a.ColumnName + ') as ' + a.ColumnName
from (
select t.ColumnName
, min(t.ColumnNo) as ColumnNo
from #sample_data as t
where t.[Year] = @Year
group by t.ColumnName
) as a
order by a.ColumnNo
for xml path ('')
),1,1,'')
);
set @DynamicQuery =
'
select isnull(b.LineName, ''Total'') as LineName
, b.' + ltrim(replace(@ColumnNameList, '| ', ', b.')) + '
from (
select a.LineName
, ' + @ColumnNameListSums + '
from (
select t.LineName
, t.ColumnName
, t.Marks
, t.[Year]
from #sample_data as t
where t.LineName <> (''Total'') --don''t need it, will generate totals later
and t.[Year] = ' + cast(@Year as char(4)) + '
) as a
pivot (max(a.Marks) for a.ColumnName in ([' + ltrim(replace(@ColumnNameList, '| ', '], [')) + '])) as a
group by grouping sets
(
(
a.LineName
)
,
(
--purposefully left empty
)
)
) as b
'
print @DynamicQuery --in order to see query being executed
exec(@DynamicQuery);
输出:
给出示例数据,将生成以下输出.
Given the sample data, the following output is generated.
+----------+-----+-----+------+
| LineName | Jay | Sam | Jack |
+----------+-----+-----+------+
| Math | 97 | 95 | 90 |
| Science | 87 | 88 | 86 |
| Total | 184 | 183 | 176 |
+----------+-----+-----+------+
SQL Server不会执行双头",因此您无法在查询的输出中获得2018.您可以在excel的第1行中手动添加"2018"的顶部标题.
SQL Server does not do "double headers", so you can't get the 2018 in the output of a query. You could manually add the top header of "2018" in row 1 in excel.
这篇关于如何使用动态SQL添加2列的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!