SQL Unpivot

• Eirikur Eiriksson

  SSC Guru

  Points: 182951

  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: 183706

  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.