Dynamic SQL sum-CodePudding

Existing in the following table:

Need to get the following table:

Each subject is not fixed, such as Chinese, maths, English, art and so on

CodePudding user response:

Choose the subjects, generate dynamic SQL exec again to get the results

CodePudding user response:

Hello World, 1/f, reference response:

selecting subjects, generating dynamic SQL to get results exec

Master, specific how to write, write along while don't come out

CodePudding user response:

How to generate dynamic SQL

CodePudding user response:

 SELECT * FROM [data] 
AS P 
The PIVOT 
(
[results] the SUM () FOR 
P. [subjects] IN [language], [math], [English]) 
) AS T

CodePudding user response:

The

reference 4 floor weixin_41985389 reply:

 SELECT * FROM [data] 
AS P 
The PIVOT 
(
[results] the SUM () FOR 
P. [subjects] IN [language], [math], [English]) 
) AS T

[language], [math], [English] is not fixed, these need to be dynamically generated

CodePudding user response:

[language], [math], [English] is not fixed, these need to be dynamically generated

CodePudding user response:

refer to 6th floor snlixing response:

[language], [math], [English] is not fixed, these need to be dynamically generated

 if (OBJECT_ID (' tempdb for.. # test1 ') is not null) drop table # test1 
Select * # into test1 from (select '3.5' clas, 't1' name, 'v1' project, 100 results union all 
Select '3.5' clas, 't2' name, 'v1' project, 090 results union all 
Select '3.5' clas, 't3' name, "v2" project, 080 results union all 
Select '3.6' clas, 't4' name, 'v1' project, 070 results union all 
Select '3.6' clas, '0' name, "v3" project, the results of 060 union all 
Select '3.6' clas, 't6' name, "v2" project, 050 results) as a 
Select * from # test1 
Declare @ sql1 nvarchar (Max); 
Select @ sql1=(select STUFF ((select distinct ', '+ project FROM # test1 FOR XML PATH ('')), 1, 1, ')) 
Select @ sql1='select * from (select clas, project, the results from # test1) as a pivot (sum (results) for Amy polumbo roject (' + @ sql1 +)) in the as B' 
The exec (@ sql1)