0

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

theteague
  • 413
  • 4
  • 10
  • You have to recognize with PIVOT. – Nikolay Fedorov May 13 '16 at 18:04
  • @NikolayFedorov thank you. Pivot is a good clue. I found this (http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) but I still need more guidance.. not sure what to do with this. – theteague May 13 '16 at 18:24

1 Answers1

1

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
Nikolay Fedorov
  • 387
  • 2
  • 7