SQL Unpivot

• yorelsemaj

  SSC Rookie

  Points: 33

  More actions

  November 23, 2015 at 3:04 pm

  #322801

  Hi All,

  Any assistance offered will be greatly appreciated:

  The sample dataset below is what I am hoping to accomplish using UNPIVOT in SQL 2008. The first two Rows contains the Heading and single row of the actual Table. Details appearing below shows the results I intend to accomplish. I manage to get the first two columns (ID & Earn and Ded) but cannot seem to get the third column to show just the Employer Taxes in the respective rows.

  ID SalaryEmployee_Tax1 Employee_Tax2Employer_Tax1 Employer_Tax2

  A001 1000 120 95 125 105

  ID Earn_DedEmployer_Tax

  A001 1000 0

  A001 120 125

  A001 95 105

• Eirikur Eiriksson

  SSC Guru

  Points: 182879

  More actions

  November 24, 2015 at 12:25 am

  #1841926

  Quick solution

  😎

  USE tempdb;

  GO

  SET NOCOUNT ON;

  DECLARE @SAMPLE TABLE (ID CHAR(4),Salary INT,Employee_Tax1 INT,Employee_Tax2 INT,Employer_Tax1 INT,Employer_Tax2 INT);

  INSERT INTO @SAMPLE(ID,Salary,Employee_Tax1,Employee_Tax2,Employer_Tax1,Employer_Tax2) VALUES ('A001',1000,120,95,125,105);

  SELECT

  X.ID

  ,X.Earn_Ded

  ,X.Employer_Tax

  FROM @SAMPLE S

  CROSS APPLY

  (

  SELECT ID,Salary,0 UNION ALL

  SELECT ID,Employee_Tax1,Employer_Tax1 UNION ALL

  SELECT ID,Employee_Tax2,Employer_Tax2

  ) AS X(ID,Earn_Ded,Employer_Tax)

  ;

  Results

  ID Earn_Ded Employer_Tax

  ---- --------- ------------

  A001 1000 0

  A001 120 125

  A001 95 105

• yorelsemaj

  SSC Rookie

  Points: 33

  More actions

  November 24, 2015 at 1:51 pm

  #1842183

  Thank SSCorpal Tunnel:

  I just realized that I left out one important component: The result should also include the "Column names" in each row:

  ID Desc Earn_Ded Employer_Tax

  ---- ---------------------------------- ------------

  A001 Salary 1000 0

  A001 Employee_Tax 120 125

  A001 Employer_Tax 95 105

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  November 24, 2015 at 1:57 pm

  #1842186

  You just need to add them to the derived table.

  DECLARE @SAMPLE TABLE (ID CHAR(4),Salary INT,Employee_Tax1 INT,Employee_Tax2 INT,Employer_Tax1 INT,Employer_Tax2 INT);

  INSERT INTO @SAMPLE(ID,Salary,Employee_Tax1,Employee_Tax2,Employer_Tax1,Employer_Tax2) VALUES ('A001',1000,120,95,125,105);

  SELECT

  X.ID

  ,X.[Desc]

  ,X.Earn_Ded

  ,X.Employer_Tax

  FROM @SAMPLE S

  CROSS APPLY

  (

  SELECT ID,'Salary', Salary,0 UNION ALL

  SELECT ID,'Employee_Tax1', Employee_Tax1,Employer_Tax1 UNION ALL

  SELECT ID,'Employee_Tax2', Employee_Tax2,Employer_Tax2

  ) AS X(ID,[Desc],Earn_Ded,Employer_Tax);

  You might want to read this article: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• yorelsemaj

  SSC Rookie

  Points: 33

  More actions

  November 24, 2015 at 2:35 pm

  #1842197

  Perfect !!

  Thank you so much.