How to use Pivot table in SQL

• Here I am going explain how and when to use PIVOT function in SQL

•  Let’s start with one example. I have one Salary table which contains three columns Emp_ID, Label_Name and Amount.  Label_Name have all salary perk for particular employee.

CREATE TABLE #SalaryDetail

(

   Emp_ID     NUMERIC,

   Label_Name VARCHAR(255),

   Amount     NUMERIC(18,2)

)

•   After that let’s fill some in data in Salary Detail table.

      INSERT INTO #SalaryDetail VALUES (1,'Basic_Salary',15000)

      INSERT INTO #SalaryDetail VALUES (1,'HRA',2000)

      INSERT INTO #SalaryDetail VALUES (1,'Conv_Allowane',800)

      INSERT INTO #SalaryDetail VALUES (2,'Basic_Salary',20000)

      INSERT INTO #SalaryDetail VALUES (2,'HRA',2000)

      INSERT INTO #SalaryDetail VALUES (2,'Conv_Allowane',800)

      Result

• The above script gives you the list of columns in string format with commas. Example:[Basic_Salary],[HRA],[Conv_Allowance]. If you pass this dynamic query to your pivot, then your pivot columns will display dynamically
• DECLARE @query AS NVARCHAR(MAX)
  DECLARE @pivot_cols NVARCHAR(1000);
  SELECT @pivot_cols =
          STUFF((SELECT DISTINCT '],[' + Label_Name
                 FROM #SalaryDetail
                 ORDER BY '],[' + Label_Name
                 FOR XML PATH('')
                 ), 1, 2, '') + ']';

•   The below SQL script creates the stored procedure which returns pivot table as output.
• SET @query =
  'SELECT * FROM
  (
      SELECT EMP_ID,Label_Name,amount
      FROM #SalaryDetail
  )Salary
  PIVOT (SUM(amount) FOR Label_Name
  IN ('+@pivot_cols+')) AS pvt'
  
  
  EXECUTE (@query)
  
  
  Output:
  
  
  
  
  

Create Window Service in C#.Net

Introduction:

• Here I am going to explain the concept of window service, what is use of window services 

Description:

• For my project I have requirement to set up services which will be update records database table every 10 second so for that I have two option in my mind either I will create exe and create schedule task or I can create window services which has been reside in services when computer booted it will automatically run every 10 second.

• Window service is best option for above scenario to run every 10 second. 

• Window Service is run automatically when computer is booted. It should be control from service control manager where they can manage start, stop and paused when needed.

• Start --> Open VS --> Files --> New Projects --> Select Window Services

• Project contains “Service1.cs” and “Program.cs” files

• In “Service1.cs” code contain the following event which can manage application Start, stop and paused when needed.

•  Now Go to Service1.cs Design page --> Right Click and “Add Installer”

• Right click on “Service process installer1” and go to “Property” and Select Account “Local System”.

• After right click on “Serviceinstaller1” and go to property and select “Start Type” as Automatic. More about “Service Installer Process” you will get from following link
•  

·           http://msdn.microsoft.com/en-us/library/ddhy0byf%28v=vs.80%29.aspx

• After configure above setting we need to write code to run window service as specific time interval.

Program.cs contain following code

• Service1.cs file overrides two method “Start” and “Stop”. I am going write code in this two method

• Writing code for Start Method

• For Above code in start method I have set interval which is run every 10 second. Now for installing window services

·         Setup  project refer following link

·         http://devblog.grinn.net/2008/02/windows-services-in-c-part-2-adding.html

Installation of Window service

•  We can install window service using “InstallUtil.exe” files

•  To install or uninstall windows service (which was created using .NET Framework) use utility InstallUtil.exe. This tool can be found in the following path (use appropriate framework version number).

·         C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe

    

• For Uninstall Window Service  "

  C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe /u "Path of EXE File"

• After installing window services. Your window is started and you can view output in log file which is reside in “d:\Service.txt” file.

Add Installer in Window Services Set up Project

• Right Click on Solution Explorer àAdd à New Project

• Select  Setup Project from Visual Studio Installer

• To tell your installer that you want the output (executable and support files) from your Windows Service project to be installed on the hard-drive, right-click on Application Folder and select Add > Project Output...

• Select Project Output file as per below figure

• select "Primary output" from the selection box. As you can see from the Description, this is the "DLL or EXE built by the project", but will also include the support files for that executable. Click Ok when you're done.

• At this point we have our Setup project that will generate an installer that adds our project's files to a folder on our computer. However, the installer won't actually install the service... yet.

 Connect Service Installer in your set up project

• Select Setup project and click on Custom Action Editor. Right click on Install Folder and click on Application folder with primary output.

Build and Install Services

·         When you're ready to test your installer, go to Build > Configuration Manager and check the Build checkbox next to your Setup Project. Then when you build it will compile as well