Need to Split Date Column into Multiple Columns by year

Question

I'm trying to split a date column into multiple columns by year. If I could achieve a roll-up subtotal at the end that would be awesome.

I'm thinking the best way to do it, is using a .NET datalist control, but I'm open to suggestions.

Example data:

DATE     | SALES
1/1/2009 | 1234.56
2/1/2009 | 4567.89
3/1/2009 | 7890.12
...
1/1/2010 | 3456.78
...
1/1/2011 | 8901.23

Desired output:

       2009     2010     2011
Jan    1234.56  3456.78  8901.23
Feb    4567.89  ...

Thanks in advance!

score 3 · Answer 1 · answered Oct 20 '11 at 14:33

3

Why not just query it as a pivot table, then display it using any front end you consider easiest and best suited.

http://msdn.microsoft.com/en-us/library/ms177410.aspx

answered Oct 20 '11 at 14:33

Sabre

2,350
2
18
25

score 2 · Accepted Answer · edited Jan 18 '21 at 12:30

2

You didn't mention your back end but as other have mentioned PIVOT syntax works in current versions of SQL and ORACLE. You can also use SUM/CASE instead if your backend doesn't support it

e.g.

SELECT MONTH([date]) AS mo, 
       SUM(CASE 
         WHEN YEAR([date]) = YEAR(Dateadd(YEAR, -2, Getdate())) THEN score
         ELSE 0 
       END)          AS y1, 
       SUM(CASE 
         WHEN YEAR([date]) = YEAR(Dateadd(YEAR, -1, Getdate())) THEN score 
         ELSE 0 
       END)           AS y2, 
       SUM(CASE 
         WHEN YEAR([date]) = YEAR(Getdate()) THEN score 
         ELSE 0 
       END)           AS y3 
FROM tablenamehere 
WHERE (vendor = 'vendornamehere') 
GROUP BY MONTH([date])

Another option is to use Linq in the C# Code to pivot the data. This is especially useful if you already have the data in memory and you don't want to go back to the DB again.

An example of this (using Jon Skeets score instead of sales) can be found at Sample for 7837475

edited Jan 18 '21 at 12:30

Community

1
1

answered Oct 20 '11 at 14:39

Conrad Frix

51,984
12
96
155

This is kinda close, but I end with a waterfall looking table. Dang comments don't support line breaks...or I'd show you what it looks like. This might help...3 years (aka columns) worth of data produces 36 rows. I need to have 3 columns with 12 rows. – s15199d Oct 20 '11 at 20:38
btw...here's my current sql "SELECT MONTH([date]) AS MO, CASE WHEN YEAR([date]) = year(dateadd(year, - 2, getdate())) THEN SALES ELSE 0 END AS Y1, CASE WHEN YEAR([date]) = year(dateadd(year, - 1, getdate())) THEN SALES ELSE 0 END AS Y2, CASE WHEN YEAR([date]) = year(getdate()) THEN SALES ELSE 0 END AS Y3 FROM tablenamehere WHERE (vendor = 'vendornamehere')" – s15199d Oct 20 '11 at 20:40
You forgot the SUMS and group by. For an example See [Sample for 7837475](http://data.stackexchange.com/stackoverflow/s/1981/sample-for-7837475) – Conrad Frix Oct 20 '11 at 20:58
Conrad, thank you so much! I really appreciate the help! The answer was right in front of me. Sometimes it's hard to see the trees for the forest. – s15199d Oct 21 '11 at 13:00

Need to Split Date Column into Multiple Columns by year

2 Answers2