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: