如何将查询结果分为代表每月数据的列? [英] How do I separate query results into columns where data from each month is represented?
问题描述
我们每个月都会建立窗口小部件.我们构建红色,绿色和蓝色小部件. 如何编写一个查询,以每月统计每种颜色产生的小部件数量?我希望结果是一个图表,其中y轴标记为Red,Green,Blue,x轴标记为Jan,Feb,Mar等.
Every month we bulid widgets. We build red, green, and blue widgets. How do I write a query that tally's the number of widgets produced of each color by month? I would like the results to be a chart with the y axis labeled Red, Green, Blue and the x axis labled Jan, Feb, Mar, etc.
年份数字无关紧要,我希望查询结果如下所示.窗口小部件的生成日期是可用数据的一部分(例如04/02/2016).我希望我可以使用datepart拉月份,但我希望将其分组为列而不是行.请帮忙吗?
The year number is irrelevant and I'd like the query results to look like below. The date the widget is produced is part of the available data (ex 04/02/2016). I'm hoping I can use datepart to pull the month but I want it to group into columns rather than rows. Please help?
推荐答案
使用以下代码:
IF OBJECT_ID('tempdb..#UsingColor','U') IS NOT NULL DROP TABLE #UsingColor;
CREATE TABLE #UsingColor
(color VARCHAR(10) NOT NULL
, usingDate DATE NOT NULL)
INSERT INTO #UsingColor(color, usingDate)
VALUES('red', '20160101')
, ('green', '20160101')
, ('blue', '20160201')
, ('red','20160201')
, ('red', '20160301')
, ('green', '20160301')
, ('blue', '20160301')
, ('orange','20160301')
, ('green', '20160401')
, ('green', '20160401')
, ('blue', '20160401')
, ('orange','20160401')
, ('blue', '20160401')
, ('green', '20160401')
, ('white', '20160401')
, ('orange','20160401')
, ('green', '20160501')
, ('white', '20160501')
, ('orange','20160601')
, ('white', '20160601')
, ('orange','20160601')
, ('green', '20160701')
, ('blue', '20160701')
, ('red','20160701')
, ('red', '20160801')
, ('green', '20160801')
, ('blue', '20160801')
, ('orange','20160801')
, ('green', '20160901')
, ('green', '20160901')
, ('blue', '20160901')
, ('orange','20160901')
, ('blue', '20160901')
, ('green', '20160901')
, ('white', '20160901')
, ('orange','20160901')
, ('green', '20161001')
, ('white', '20161001')
, ('orange','20161101')
, ('white', '20161101')
, ('orange','20161101')
--, ('black', '20161201')
SELECT color
, [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
, [12] AS Dec
FROM(
SELECT color, 1 AS cntr, MONTH(usingDate) AS m FROM #UsingColor) AS D
PIVOT(COUNT(cntr) FOR m IN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) AS P
这篇关于如何将查询结果分为代表每月数据的列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!