Create Summary view of SQL Datatable

By Eranda -


Combine two rows into one row with same ID


Go through the following SQL table named[EmployeeSales]. Think that you have to create a summary table of monthly sales against each employee ID. 

EmpID
Year
Month
Sales
0001
2012
May
0.5
0001
2012
June
0.8
0001
2012
July
0.7
0002
2012
May
1.1
0002
2012
June
0.9
0003
2012
May
0.4
0003
2012
June
0.5
0003
2012
July
0.5
[EmployeeSales] Table


For that You can write a SQL query as follows.
In that Query I have filter sales records relate to year 2012 and sord according to EmpID. 




SELECT EmpID
, MAX(CASE WHEN [Month] = 'May' THEN [Sales] ELSE NULL END) AS [May]
, MAX(CASE WHEN [Month] = 'June' THEN [Sales] ELSE NULL END) AS [June]
, MAX(CASE WHEN [Month] = 'July' THEN [Sales] ELSE NULL END) AS [July]
  FROM EmployeeSales  WHERE [YEAR] = 2012
GROUP BY EmpID
ORDER BY EmpID






EmpID
May
June
July
0001
0.5
0.8
0.7
0002
1.1
0.9
Null
0003
0.4
0.5
0.5
[Result] Table

Comments

Post a Comment

Popular posts from this blog

Apply CSS styles to SharePoint Web parts

By Eranda -
Image
In SharePoint we are using various types of web parts. List View web part are one of major type which uses to populate data on the web page from a SharePoint list. By default all the web parts of the page use the styles inherits from the site theme. Here I explain a simple workout to apply a CSS style to a web part only using out of the box features of SharePoint 2010. There are only 3 steps for you to follow. 1. Get the ID of web part you wants to apply CSS style 2. Add Content Editor web part 3. Apply CSS style as you required Step 1 ♣ Go to “view source” of the browser you are using and search for the web parts name. In my scenario, it’s “Emergency”.   ♣ Now grab the ID number mentioned there at the end of WebPartTitleWPQ 2 . “2” is the ID of “Emergency” web part in my web page. Step 2 ♣ Now add a Content Editor Web Part by go to edit mode of the page. ♣ Set the Chrome Type as None of the added Content Editor Web Part. Step
Read more

SharePoint Number Column Without Commas

By -
Image
Avoiding thousand separator(Comma) in SharePoint Number field SharePoint number fields having thousand separators(Comma) which may not want us to need every time. But there are no possibility to avoid the thousand separator(Comma) in the number fields.  To avoid this comma we can do a small workout as following.. Suppose our Number field named as "Student No." Create another Calculated column and name it as "Student Number" Then you have to type the formula according to the following format     = TEXT( Number_Column_Name, "0") And click Save In our scenario it will show as =TEXT([Student No],"0") Note : If the Student No field will empty, the value will be shown as "0".... Or if you want to keep it blank you have to adjust the formula as   =IF( [Student No] <>"",TEXT( [Student No] ,"0"),"") 
Read more

Use of Information Rights Management (IRM) in SharePoint

By Eranda -
SharePoint is a Collaborating web platform provider which helps us to share documents and other information with the users of the SharePoint site. Information security is a main concern when sharing documents with various people. Information Right Management server facilitate to overcome the information right violation and restrict the access of the documents. Followings are the main advantages of IRM which mentioned on Microsoft.com site 1. Helps to prevent an authorized viewer from copying, modifying, printing, faxing, or copying and pasting the content for unauthorized use 2. Helps to prevent an authorized viewer from copying the content by using the Print Screen feature in Microsoft Windows 3. Helps to prevent an unauthorized viewer from viewing the content if it is sent in e-mail after it is downloaded from the server 4. Restricts access to content to a specified period of time, after which users must confirm their credentials and download the content again ·   
Read more