March 15, 2010 at 9:17 am
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?
March 15, 2010 at 9:25 am
looks like a text-book case for a pivot query ... bing "sql server pivot"
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 15, 2010 at 10:13 am
thanks PaulB-TheOneAndOnly, i will look into that.
have a nice day!
March 15, 2010 at 11:52 am
I think I have a solution although it is not pretty and probably not the most efficient. I wrote some T-SQL to accomplish what you needed. Following your example, I created a table called SqlTest which contains the following columns.
OrderDate (smalldatetime)
Quantity (int)
Using your example data and basic string manipulation (DATEPART, DATENAME, LEFT) along with temp tables, I offer the following code:
------------------------------------------------------------------------------------
-- First, parse out Year, 3 character Month and put into temp table
SELECT OrderDate, DATEPART(YEAR,OrderDate) AS OrderYear, LEFT(DATENAME(MONTH,OrderDate),3) AS OrderMonth, Quantity
INTO #tempSqlTest1
FROM SQLtest
------------------------------------------------------------------------------------
-- Next, attach values for each month
SELECT OrderYear, OrderMonth,
(CASE [OrderMonth]
WHEN 'Jan' THEN Quantity
ELSE 0
END) AS Jan,
(CASE [OrderMonth]
WHEN 'Feb' THEN Quantity
ELSE 0
END) AS Feb,
(CASE [OrderMonth]
WHEN 'Mar' THEN Quantity
ELSE 0
END) AS Mar,
(CASE [OrderMonth]
WHEN 'Apr' THEN Quantity
ELSE 0
END) AS Apr,
(CASE [OrderMonth]
WHEN 'May' THEN Quantity
ELSE 0
END) AS May,
(CASE [OrderMonth]
WHEN 'Jun' THEN Quantity
ELSE 0
END) AS Jun,
(CASE [OrderMonth]
WHEN 'Jul' THEN Quantity
ELSE 0
END) AS Jul,
(CASE [OrderMonth]
WHEN 'Aug' THEN Quantity
ELSE 0
END) AS Aug,
(CASE [OrderMonth]
WHEN 'Sep' THEN Quantity
ELSE 0
END) AS Sep,
(CASE [OrderMonth]
WHEN 'Oct' THEN Quantity
ELSE 0
END) AS Oct,
(CASE [OrderMonth]
WHEN 'Nov' THEN Quantity
ELSE 0
END) AS Nov,
(CASE [OrderMonth]
WHEN 'Dec' THEN Quantity
ELSE 0
END) AS [Dec]
into #tempSqlTest2
From #tempSqlTest1
---------------------------------------------------------------------------------------
-- Finally, agggregate data by grouping each year and SUMming the individual months
SELECT OrderYear, SUM(Jan) AS Jan, SUM(Feb) AS Feb, SUM(Mar) AS Mar, SUM(Apr) AS Apr,
SUM(May) AS May, SUM(Jun) AS Jun, SUM(Jul) AS Jul, SUM(Aug) AS Aug,
SUM(Sep) AS Sep, SUM(Oct) AS Oct, SUM(Nov) AS Nov, SUM([Dec]) AS [Dec]
FROM #tempSqlTest2
GROUP BY OrderYear
ORDER BY OrderYear
March 15, 2010 at 1:37 pm
monzulu
--To make you data available for testing
CREATE TABLE #T(OrderDate DATETIME, Quantity INT )
INSERT INTO #T
SELECT '1/1/2009', 1 UNION ALL
SELECT '2/1/2009', 3 UNION ALL
SELECT '5/1/2009', 7 UNION ALL
SELECT '9/1/2009', 2 UNION ALL
SELECT '1/1/2010', 4 UNION ALL
SELECT '2/1/2010', 1 UNION ALL
SELECT '3/1/2010', 8
--
--==To extract year, month from your DATETIME entry
--
CREATE TABLE #T2(Yr VARCHAR(4),Mn INT,Qty INT)
INSERT INTO #T2
SELECT DATEPART(yyyy,OrderDate),DATEPART(mm,OrderDate),SUM(Quantity) AS 'Quan' FROM #T
GROUP BY DATEPART(yyyy,OrderDate),DATEPART(mm,OrderDate)
ORDER BY DATEPART(yyyy,OrderDate),DATEPART(mm,OrderDate)
--
--==Now to get the resutls
--
SELECT Yr,[1] AS 'January', [2] AS 'Februrary',[3] AS 'March',[4] AS 'April',[5] AS 'May',[9] AS' November'
FROM (SELECT Yr,Mn,Qty FROM #T2) p
PIVOT
(SUM(Qty)
FOR Mn IN
( [1], [2], [3], [4], [5],[9] )
) AS pvt
ORDER BY Yr
--
--===Results
--
YrJanuaryFebruraryMarchAprilMay November
200913 NULL NULL 7 2
201041 8 NULL NULL NULL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply