Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
select year1,
max(case when Month1=’jan’ then Amount end) as jan,
max(case when Month1=’feb’ then Amount end) as feb,
max(case when Month1=’mar’ then Amount end) as Mar
from #TestTranspose
group by Year1
LikeLiked by 2 people
select Yr as [Year], [Jan] as [January], [Feb] as [February], [Mar] as [March]
from (select Yr, Mth, Amt from tblPivotPuzzle) as T
pivot
(
sum(Amt)
for Mth in ([Jan], [Feb], [Mar])
) as PT
LikeLike