hi guys i have this table. i've tried things but i am not skilled enough to
make it to work.

date quantity
1/1/2009 1
2/1/2009 3
5/1/2009 7
9/1/2009 2
1/1/2010 4
2/1/2010 1
3/1/2010 8

and would like to have a recordset to look like this:

year jan feb mar april may jun jul aug sep oct nov dec
2009 1 3 0 0 7 0 0 0 2 0 0 0
2010 4 1 8

thanks for any ideas?

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...erver/201003/1

Here is one method. You can use COALESCE on all columns if you want to replace NULLs with 0 (as it was done for December).

SELECT year_nbr,
[1] AS jan,
[2] AS feb,
[3] AS mar,
[4] AS apr,
[5] AS may,
[6] AS jun,
[7] AS jul,
[8] AS aug,
[9] AS sep,
[10] AS oct,
[11] AS nov,
COALESCE([12], 0) AS dec
FROM (
SELECT YEAR([date]) AS year_nbr, MONTH([date]) AS month_nbr, quantity
FROM Foo ) AS F
PIVOT
(SUM(quantity) FOR month_nbr IN (
[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) AS P;

--
Plamen Ratchev
http://www.SQLStudio.com

thanks Plamen Ratchev, you just made my day. have a nice day!!!


Plamen Ratchev wrote:
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...erver/201003/1

Here's a working example:


USE tempdb

IF OBJECT_ID('tempdb..#T') IS NOT NULL
BEGIN
DROP TABLE #T
END

CREATE TABLE #T
(
TDate DATETIME,
TQuantity INT
)

INSERT INTO
#T (TDate, TQuantity)
SELECT
'1/1/2009',
1
UNION
SELECT
'2/1/2009',
3
UNION
SELECT
'5/1/2009',
7
UNION
SELECT
'9/1/2009',
2
UNION
SELECT
'1/1/2010',
4
UNION
SELECT
'2/1/2010',
1
UNION
SELECT
'3/1/2010',
8

/*
SELECT
YEAR(TDate) AS [YearValue],
MONTH(TDate) AS [MonthValue],
SUM(TQuantity) AS [Quantity]
FROM
#T
GROUP BY
YEAR(TDate),
MONTH(TDate),
DATENAME(MONTH, TDate)
ORDER BY
YEAR(TDate),
MONTH(TDate)
--*/


SELECT
[YearValue],
COALESCE([1], 0) AS January,
COALESCE([2], 0) AS February,
COALESCE([3], 0) AS March,
COALESCE([4], 0) AS April,
COALESCE([5], 0) AS May,
COALESCE([6], 0) AS June,
COALESCE([7], 0) AS July,
COALESCE([8], 0) AS August,
COALESCE([9], 0) AS September,
COALESCE([10], 0) AS October,
COALESCE([11], 0) AS November,
COALESCE([12], 0) AS December
FROM
(
SELECT
YEAR(TDate) AS [YearValue],
MONTH(TDate) AS [MonthValue],
TQuantity
FROM
#T) AS SourceQuery PIVOT ( SUM([TQuantity]) FOR [MonthValue]
IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) )
AS PivotData
ORDER BY
[YearValue]


DROP TABLE #T

-Eric Isaacs