sql server - How to use dynamic SQL to add value of 2 columns

Question

I have small table which contains students marks. Table data is shown in below image.

It is look like below in excel

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.

Please refer below code:

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

Answer 1

Taking the excel screenshot to be the expected output, you could accomplish this with just specifying the Year of interest.

Sample Data:

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)

Answer:

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);

Output:

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 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.