• This is quick and dirty, but since the number of months in a year isn't going to change any time soon you can do this:

    ;With Invoice AS

    (

    SELECT '20130101' [InvoiceDate], 100 [InvoiceAmount] UNION ALL

    SELECT '20120102', 101 UNION ALL

    SELECT '20120202', 140 UNION ALL

    SELECT '20120302', 130 UNION ALL

    SELECT '20120402', 120 UNION ALL

    SELECT '20120502', 110 UNION ALL

    SELECT '20130102', 101 UNION ALL

    SELECT '20130202', 140 UNION ALL

    SELECT '20130302', 130 UNION ALL

    SELECT '20130402', 120 UNION ALL

    SELECT '20130502', 110 UNION ALL

    SELECT '20130502', 111

    )

    SELECT *,ISNULL(jan,0) +

    ISNULL(feb,0) +

    ISNULL(mar,0) +

    ISNULL(apr,0) +

    ISNULL(may,0) +

    ISNULL(jun,0) +

    ISNULL(jul,0) +

    ISNULL(aug,0) +

    ISNULL(sep,0) +

    ISNULL(oct,0) +

    ISNULL(nov,0) +

    ISNULL(dec,0) [YearToDate]

    FROM (

    SELECT

    year(CONVERT(DATE,InvoiceDate)) as [year],left(datename(month,CONVERT(DATE,InvoiceDate)),3)as [month],

    InvoiceAmount as Amount

    FROM Invoice

    ) as s

    PIVOT

    (

    SUM(Amount)

    FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)

    )AS [pivot]

    SQL SERVER Central Forum Etiquette[/url]