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

How to use dynamic SQL to add value of 2 columns

user1290
1#
user1290 Published in May 23, 2018, 6:36 pm

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
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO