周一新干劲 - 6/9/14 - Sharepoint Report Creation - Stuff(), Join, Case in SQL

新的一周，这周末精神保持的还很好，好好干吧！也就三周了！

上午活还挺多，要create一个新的report给graduation application，这个很好啊，造福于同学们，主要工作在SQL语句设计上，我要join几个table调用不同信息，最后把每个学生申请的专业，小专业和具体方向放到record的主表之中。有一步要把多个cell的内容连接放到一个里面，于是就得用到了for xml 这样的语句，还有stuff（）string insertion。代码如下：

SELECT [id] as [ID], [studentID] as [Student ID], [firstName] as [First Name],[lastName] as [Last Name], [reqDate] as [Request Date], [email] as [Email],[gradTerm] as [Graduation Term],[honorsCollege] as [Honors College],(STUFF ((SELECT majorName+', '    FROM [GradApp_Degrees] where [GradApp_Degrees].studentID=Records.studentID for xml path ('')), 1, 0,' ') +  STUFF ((SELECT minor+', '    FROM [GradApp_Minors] where [GradApp_Minors].studentID=Records.studentID for xml path ('')), 1, 0,' ')) as [Applied Minor] , STUFF ((SELECT specialization+', ' FROM [GradApp_Degrees] where [GradApp_Degrees].studentID=Records.studentID for xml path ('')), 1, 0,' ') as [Specialization] FROM [MyState_CLG].[dbo].[GradApp_Records] as Records

下午明白了不是要简单的把两个cell内容连起，是要有小专业的attach到大专业去，这样我就傻逼了，于是就琢磨了一个小时，开始想用case when then end来判断，后来发现stuff（）method不能嵌套使用，于是就想到了另外加一个column，把三个table join之后select得到的内容，然后join来join去又花了半个小时，最后得出正确结果。

SELECT [id] as [ID], 
[studentID] as [Student ID], 
[firstName] as [First Name],
[lastName] as [Last Name], 
[reqDate] as [Request Date], 
[email] as [Email],
[gradTerm] as [Graduation Term],
[honorsCollege] as [Honors College],
STUFF ( 
(SELECT distinct Degree.majorName + '; '
FROM [MyState_CLG].[dbo].[GradApp_Degrees] Degree 
join [MyState_CLG].[dbo].[GradApp_Records] Records
on Degree.studentID=Records.studentID  for xml path ('')), 1, 0,' ') as [Applied Major] , 
STUFF ( 
(SELECT distinct Degree.majorName + '('+Minors.minor
+'); 'FROM [MyState_CLG].[dbo].[GradApp_Degrees] Degree join [MyState_CLG].[dbo].[GradApp_Minors] Minors
on Degree.majorName	= Minors.majorName and Minors.studentID = Degree.studentID
join [MyState_CLG].[dbo].[GradApp_Records] Records
on Degree.studentID=Records.studentID  for xml path ('')), 1, 0,' ') as[Applied Minor] , 
STUFF ((SELECT distinct specialization+'; ' FROM [MyState_CLG].[dbo].[GradApp_Degrees] 
where [MyState_CLG].[dbo].[GradApp_Degrees].studentID=Records.studentID for xml path ('')), 1, 0,' ') as [Specialization] 
FROM [MyState_CLG].[dbo].[GradApp_Records] as Records

今下午insidestate的server看来是正在升级，于是干不了report的事了，从今天开始学校就开始用sharepoint 2013了！

SQL里面有时候报错说找不到object，是因为没有put full DB名字，可以两种方法，在一组服务器上邮件new query，或者在new query里面加上use webforms就可以了。

最后把一个mockup做了，一个印第安orientation的申请表，挺有意思。