Code implementation SQL Server dynamic row to column without stored procedure

In a two-step query, the first step is to query the dynamic column, and the second step uses the pivot function.

Code:

List<datatable> datatablelist =NewList<datatable>();#regionIndexDataTable Dtevaitemtitle= db. Runtable<edu_evaluationrecord> (string. Format (@"SELECT DISTINCT eva.id, eva.name from Edu_evaluationrecord R left joins Edu_qnvolume V on R.queryid=v.id Left join Sys_evaluationitem Eva in r.evaitemid=eva.id where v.id={0} ORDER by Eva.id", volumeeva.id)); List<string> evaitemtitlelist =Newlist<string>(); for(inti =0; i < DtEvaItemTitle.Rows.Count; i++) {Evaitemtitlelist.add (string. Format ("[{0}]", dtevaitemtitle.rows[i]["name"]. ToString ()));}stringEvaitemtitles = String.Join (",", Evaitemtitlelist.toarray ());stringsql =string. Format (@"SELECT * FROM (select Stu.name as name, Stu. Stunum as study number, Stu. Examnum as test number, case when Stu. sex= ' 1 ' then ' men ' else ' female ' end as surname, case when Stu. brothers= ' 1 ' then ' no ' else ' is ' end as ' is only, Stu. Eduf as Father culture, Stu. EduM as mother culture, R.score as Fraction, Eva. Name as topic from Edu_evaluationrecord R left joins Edu_student Stu on r.userid=stu.id left joins Sys_user u on stu.u Serid=u.id left joins Edu_qnvolume V on R.queryid=v.id left joins Sys_evaluationitem Eva on R.evaitemid=eva.id wher e v.id={0}) p Pivot (sum (fractional) for topic in ({1})) as Pvt", Volumeeva.id, evaitemtitles);D atatable DT= db. Runtable<edu_evaluationrecord>(SQL);d T. TableName=volumeeva.name;datatablelist.add (DT);#endregion#regionOrdinaryDataTable Dtitemtitle= db. Runtable<edu_evaluationrecord> (string. Format (@"SELECT DISTINCT s.id, s.title from Edu_qnanswer ans left joins Edu_qnsubject s on S.id=ans.subjectid left Join Edu_qnvolume V on s.volumeid=v.id where v.id={0} ORDER by S.id", volumegeneral.id)); List<string> itemtitlelist =Newlist<string>(); for(inti =0; i < DtItemTitle.Rows.Count; i++) {Itemtitlelist.add (string. Format ("[{0}]", dtitemtitle.rows[i]["title"]. ToString ()));}stringItemtitles = String.Join (",", Itemtitlelist.toarray ()); SQL=string. Format (@"SELECT * FROM (select Stu.name as name, Stu. Stunum as study number, Stu. Examnum as test number, case when Stu. sex= ' 1 ' then ' men ' else ' female ' end as surname, case when Stu. brothers= ' 1 ' then ' no ' else ' is ' end as ' is only, Stu. Eduf as Father culture, Stu. EduM as mother culture, ans. Answercontent as option, s.title as topic from edu_qnanswer ans left join Sys_user u on ans.answerid=u.id left join edu_s Tudent Stu on Stu.userid=u.id left joins Edu_qnsubject s on S.id=ans.subjectid left joins Edu_qnvolume v on S.volumeid =v.id where v.id={0}) p Pivot (max (option) for topic in ({1})) as Pvt", Volumegeneral.id, itemtitles);d T= db. Runtable<edu_evaluationrecord>(SQL);d T. TableName=volumegeneral.name;datatablelist.add (DT);#endregion

View Code

Code implementation SQL Server dynamic row to column without stored procedure