This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to display data basis on year and month. This is a puzzle of PIVOT like form the rows into columns.
Input Data:
|
1 2 3 4 5 6 |
YearData MonthData Amount ----------- --------- ----------- 2016 Jan 10000 2016 Feb 15000 2016 Mar 13000 2017 Jan 17000 |
Expected Output:
|
1 2 3 4 |
YearData Jan Feb Mar ----------- ----------- ----------- ----------- 2016 10000 15000 13000 2017 17000 NULL NULL |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE YearMonth ( YearData INT ,MonthData CHAR(3) ,Amount INT ) GO INSERT INTO YearMonth Values (2016,'Jan',10000) ,(2016,'Feb',15000) ,(2016,'Mar',13000) ,(2017,'Jan',17000) GO |
Solution 1: Without using PIVOT
|
1 2 3 4 5 |
select YearData, sum(case when MonthData = 'Jan' then amount else null end) as Jan, sum(case when MonthData = 'Feb' then amount else null end) as Feb, sum(case when MonthData = 'Mar' then amount else null end) as Mar from YearMonth group by YearData |
Solution 2: Using PIVOT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
;WITH CTE AS ( SELECT 1 rnk , 'Jan' Mm UNION ALL SELECT 2 , 'Feb' UNION ALL SELECT 3 , 'Mar' ) ,CTE3 AS ( SELECT T.* , rnk FROM YearMonth T INNER JOIN CTE c ON C.Mm = T.MonthData ) ,CTE4 AS ( SELECT YearData,[1] Jan ,[2] Feb ,[3] Mar FROM CTE3 PIVOT (SUM(Amount) FOR rnk IN ([1],[2],[3])) p ) SELECT YearData , SUM(Jan) Jan , SUM(Feb) Feb , SUM(Mar) Mar FROM CTE4 GROUP BY YearData |
Please try the different solution for this puzzle and share it via comment...
