November 23, 2015 at 3:04 pm
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
November 24, 2015 at 12:25 am
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
November 24, 2015 at 1:51 pm
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
November 24, 2015 at 1:57 pm
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/
November 24, 2015 at 2:35 pm
Perfect !!
Thank you so much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply