Home How to use dynamic SQL to add value of 2 columns

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