T-SQL Query | [ Year & Month wise data – The Pivot Puzzle ]  – In this puzzle we have to transpose the data to show year and month wise data. Please check out the sample input and expected output for details.

Sample Input

 Year1 Month1 Amount 2014 Jan 1000 2014 Feb 1200 2014 Mar 1300 2013 Jan 3000

Expected Output

 Year1 Jan Feb Mar 2013 3000 NULL NULL 2014 1000 1200 1300

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.

Script

Use the below script to generate the source table and fill them up with the sample data.

 ``` --Create table CREATE TABLE TestTranspose ( Year1 INT ,Month1 VARCHAR(3) ,Amount INT ) GO --Insert Data INSERT INTO TestTranspose Values (2014,'Jan',1000),(2014,'Feb',1200),(2014,'Mar',1300) INSERT INTO TestTranspose Values (2013,'Jan',3000) --Verify Data SELECT Year1,Month1,Amount FROM TestTranspose ```

UPDATE – 21-Apr-2015 – Solution 1

 ``` -- --Solution 1 ;WITH CTE AS ( SELECT 1 rnk , 'Jan' Mm UNION ALL SELECT 2 , 'Feb' UNION ALL SELECT 3 , 'Mar' ) ,CTE3 AS ( SELECT T.* , rnk FROM TestTranspose T INNER JOIN CTE c ON C.Mm = T.Month1 ) ,CTE4 AS ( SELECT Year1,[1] Jan ,[2] Feb ,[3] Mar FROM CTE3 PIVOT (SUM(Amount) FOR rnk IN ([1],[2],[3])) p ) SELECT Year1 , SUM(Jan) Jan , SUM(Feb) Feb , SUM(Mar) Mar FROM CTE4 GROUP BY Year1 -- ```

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com