SQL Break a column of dates up by year?

Question

This is probably an easy one, I am just having a brain fart. I have a column of dates covering several years. How can I make a query to break it up by year? For instance:

Code - Date       - CustName    - Sales
1001 - 2011-01-12 - Sparkies    - 50
1002 - 2012-02-11 - Spankies    - 125
1001 - 2013-01-12 - Sparkies    - 60
1003 - 2011-05-05 - Squirrelies - 75

I would like a report to show:

Code - CustName    - 2011 - 2012 - 2013
1001 - Sparkies    - 50   - 0    - 60
1002 - Spankies    - 0    - 125  - 0
1003 - Squirrelies - 75   - 0    - 0

This is in MSSQL 2008 R2. Thanks in advance...

Will there ever be more than one row per year for a given `CustName`? If so, what's the final value to be for that row/column? Sum? Min? Max? Average? — Damien_The_Unbeliever, Dec 11 '13 at 14:50
-1 Please update your question with what you've tried, and why it didn't work. — Kermit, Dec 11 '13 at 14:53

score 3 · Answer 1 · answered Dec 11 '13 at 15:05

You can use the PIVOT function to get the result. If you have a limited number of values, then you would hard-code the query similar to:

select code, custname,
  [2011], [2012], [2013]
from
(
  select code, year(date) dt,
    custname, sales
  from yourtable
) d
pivot
(
  sum(sales)
  for dt in ([2011], [2012], [2013])
) piv;

See SQL Fiddle with Demo

But if you have an unknown number of years, then you would need to look at using dynamic SQL to get the final result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(year(date)) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT code, custname, ' + @cols + ' 
            from 
            (
              select code, year(date) dt,
                custname, sales
              from yourtable
            ) x
            pivot 
            (
                sum(sales)
                for dt in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo. These will give a result of:

| CODE |    CUSTNAME |   2011 |   2012 |   2013 |
|------|-------------|--------|--------|--------|
| 1001 |    Sparkies |     50 | (null) |     60 |
| 1002 |    Spankies | (null) |    125 | (null) |
| 1003 | Squirrelies |     75 | (null) | (null) |

score 1 · Answer 2 · answered Dec 11 '13 at 14:47

1

You could use something like:

SELECT
    [Code],
    [CustName],
    SUM(CASE WHEN YEAR([Date]) = 2011 THEN [Sales] ELSE 0 END) AS [2011],
    SUM(CASE WHEN YEAR([Date]) = 2012 THEN [Sales] ELSE 0 END) AS [2012],
    SUM(CASE WHEN YEAR([Date]) = 2013 THEN [Sales] ELSE 0 END) AS [2013]
FROM
    [Table]
GROUP BY
    [Code],
    [CustName]

Or, if you feel like it, look at using PIVOT.

answered Dec 11 '13 at 14:47

Alex Humphrey

6,099
4
26
41

This is not scalable. – Kermit Dec 11 '13 at 14:53
@FreshPrinceOfSO What do you mean by 'scalable', and what alternatives are there? – Alex Humphrey Dec 11 '13 at 14:58
This will work if the OP only has 3 years to work with. If you want it to be reusable and scalable, then a `PIVOT` is the solution. – Kermit Dec 11 '13 at 14:59
This does work, but as was mentioned, the dates being hard-coded, what happens when a 2010 and 2014 need a coulum? I am looking into PIVOT now... – user3059028 Dec 11 '13 at 15:02
@FreshPrinceOfSO as far as I am aware, `PIVOT` suffers from the same problem, at least in SQL Server. A `PIVOT` resulting in a dynamic number of columns requires dynamic SQL, same as this. That is, unless there's been an update that I'm unaware of. – Alex Humphrey Dec 11 '13 at 15:02
@user3059028 I think `PIVOT` has the same problems. You'll need to resort to dynamically forming a query like my example above, or similarly using a `PIVOT` query. See http://stackoverflow.com/questions/2922797/t-sql-pivot-possibility-of-creating-table-columns-from-row-values – Alex Humphrey Dec 11 '13 at 15:04
Meant to say *dynamic* pivot; my apologies. – Kermit Dec 11 '13 at 15:05

score -1 · Answer 3 · answered Dec 11 '13 at 14:49

-1

SELECT CAST(YEAR(Date) AS VARCHAR(4)) AS Year, SUM(Sales) AS Sales 
FROM Orders
GROUP BY CAST(YEAR(Date) AS VARCHAR(4))
ORDER BY Year

answered Dec 11 '13 at 14:49

Smeghead Sev

418
4
14

The OP is looking for a `PIVOT`. – Kermit Dec 11 '13 at 14:52
Would a pivot really work? You talk about the query being scaling but if you use a pivot you would still have to list out the columns just like @Alex Humphrey's query. – Smeghead Sev Dec 11 '13 at 15:06
At the least, what's required here is something like Alex Humphrey's answer. What should really be used here is the dynamic pivot solution provided by bluefeet. – Kermit Dec 11 '13 at 16:07

SQL Break a column of dates up by year?

3 Answers3