Search code examples
t-sqlsql-server-2012-datatools

How do I separate query results into columns where data from each month is represented?

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.

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?

Example

Solution

  • Use this code:

    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