Home How to use dynamic SQL to add value of 2 columns
Reply: 1

How to use dynamic SQL to add value of 2 columns

Jay Desai
1#
Jay Desai Published in 2018-02-05 00:22:34Z

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
tarheel
2#
tarheel Reply to 2018-02-05 06:35:13Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.410563 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO