0

I have this Table

Income Table,

I want the data grouped by year, as the following table show

Desired Table

I tried this codes

SELECT * 
FROM    
(
  SELECT Year, Income 
 FROM Sales 
) t 
pivot 
(
   count([Income]) for[Income]in ([1],[2],[3])
) as event_count;

However I'm getting Null Values

Year  1  2  3
2015  0  0  0

What is best way to achieve the desired result? Please forgive me, the way I arranged the question as I'm new in this forum.

Taryn
  • 242,637
  • 56
  • 362
  • 405
Sandet
  • 21
  • 3
  • I think the answer here addresses your question: http://stackoverflow.com/questions/2255640/mysql-reshape-data-from-long-tall-to-wide – Mako212 Apr 07 '16 at 19:13
  • sub query should be like `SELECT Year, Income, Row_Number() OVER (Partition By Year Order By Income) Rn` then in your pivot use `MAX([Income]) for [Rn] in ([1],[2],[3])` – JamieD77 Apr 07 '16 at 19:52
  • Thank for the answer, however I have solved the question through this thread: http://stackoverflow.com/questions/20999264/transpose-rows-and-columns-with-no-aggregate – Sandet Apr 07 '16 at 21:27

0 Answers0