Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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