如何使用动态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屋！