How can I make "month" columns in Sql?

Question

I've got a set of data that looks something like this (VERY simplified):

productId    Qty   dateOrdered
---------    ---   -----------
       1       2    10/10/2008
       1       1    11/10/2008
       1       2    10/10/2009
       2       3    10/12/2009
       1       1    10/15/2009
       2       2    11/15/2009

Out of this, we're trying to create a query to get something like:

productId  Year  Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
---------  ----  --- --- --- --- --- --- --- --- --- --- --- ---
        1  2008    0   0   0   0   0   0   0   0   0   2   1   0
        1  2009    0   0   0   0   0   0   0   0   0   3   0   0
        2  2009    0   0   0   0   0   0   0   0   0   3   2   0

The way I'm doing this now, I'm doing 12 selects, one for each month, and putting those in temp tables. I then do a giant join. Everything works, but this guy is dog slow.

Answer 1

For those using Big Query, you can use the following:

select *
from UNNEST(GENERATE_DATE_ARRAY('2015-10-01', '2019-10-01', INTERVAL 1 MONTH))

See https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=fr#generate_date_array

Answer 2

try this. So this code will select data within certain time range, then convert it to a new column. For example, in my sql code: it selects time range between '2014-10-01' and '2014-10-31' from column 'L_dt', then create a new column called "October". In this way, we can lay out data at different columns originated from one column.

select 
sum(case when L_dt between '2014-10-01' and '2014-10-31' then 1 else 0 end) October,
sum(case when L_dt between '2014-11-01' and '2014-11-30' then 1 else 0 end) November,
sum(case when L_dt between '2014-12-01' and '2014-12-31' then 1 else 0 end) December
from Table; 

If the input looks like: L_dt
2014-10-13 2014-12-21 2014-11-22 2014-10-10

Then the output will be

+---------+----------+----------+
| October | November | December |
+---------+----------+----------+
|    2    |    1     |    1     |
+---------+----------+----------+

Answer 3

select productId, Year(dateOrdered) Year
  ,isnull(sum(case when month(dateOrdered) = 1 then Qty end), 0) Jan
  ,isnull(sum(case when month(dateOrdered) = 2 then Qty end), 0) Feb 
  ,isnull(sum(case when month(dateOrdered) = 3 then Qty end), 0) Mar
  ,isnull(sum(case when month(dateOrdered) = 4 then Qty end), 0) Apr
  ,isnull(sum(case when month(dateOrdered) = 5 then Qty end), 0) May
  ,isnull(sum(case when month(dateOrdered) = 6 then Qty end), 0) Jun
  ,isnull(sum(case when month(dateOrdered) = 7 then Qty end), 0) Jul
  ,isnull(sum(case when month(dateOrdered) = 8 then Qty end), 0) Aug
  ,isnull(sum(case when month(dateOrdered) = 9 then Qty end), 0) Sep
  ,isnull(sum(case when month(dateOrdered) = 10 then Qty end), 0) Oct
  ,isnull(sum(case when month(dateOrdered) = 11 then Qty end), 0) Nov
  ,isnull(sum(case when month(dateOrdered) = 12 then Qty end), 0) Dec
from Table1
group by productId, Year(dateOrdered)

SQL Fiddle

Answer 4

SELECT productId, YEAR,
ISNULL((SELECT SUM(Qty) FROM Product WHERE productId=X.productId AND YEAR=YEAR(dateOrdered) AND MONTH(dateOrdered)=1),0) as 'JAN',
ISNULL((SELECT SUM(Qty) FROM Product WHERE productId=X.productId AND YEAR=YEAR(dateOrdered) AND MONTH(dateOrdered)=2),0) as 'FEB',
ISNULL((SELECT SUM(Qty) FROM Product WHERE productId=X.productId AND YEAR=YEAR(dateOrdered) AND MONTH(dateOrdered)=3),0) as 'MAR',
ISNULL((SELECT SUM(Qty) FROM Product WHERE productId=X.productId AND YEAR=YEAR(dateOrdered) AND MONTH(dateOrdered)=4),0) as 'APR',
ISNULL((SELECT SUM(Qty) FROM Product WHERE productId=X.productId AND YEAR=YEAR(dateOrdered) AND MONTH(dateOrdered)=5),0) as 'MAY',
ISNULL((SELECT SUM(Qty) FROM Product WHERE productId=X.productId AND YEAR=YEAR(dateOrdered) AND MONTH(dateOrdered)=6),0) as 'JUN',
ISNULL((SELECT SUM(Qty) FROM Product WHERE productId=X.productId AND YEAR=YEAR(dateOrdered) AND MONTH(dateOrdered)=7),0) as 'JUL',
ISNULL((SELECT SUM(Qty) FROM Product WHERE productId=X.productId AND YEAR=YEAR(dateOrdered) AND MONTH(dateOrdered)=8),0) as 'AUG',
ISNULL((SELECT SUM(Qty) FROM Product WHERE productId=X.productId AND YEAR=YEAR(dateOrdered) AND MONTH(dateOrdered)=9),0) as 'SEP',
ISNULL((SELECT SUM(Qty) FROM Product WHERE productId=X.productId AND YEAR=YEAR(dateOrdered) AND MONTH(dateOrdered)=10),0) as 'OCT',
ISNULL((SELECT SUM(Qty) FROM Product WHERE productId=X.productId AND YEAR=YEAR(dateOrdered) AND MONTH(dateOrdered)=11),0) as 'NOV',
ISNULL((SELECT SUM(Qty) FROM Product WHERE productId=X.productId AND YEAR=YEAR(dateOrdered) AND MONTH(dateOrdered)=12),0) as 'DEC'
FROM (
SELECT productId, YEAR(dateOrdered) AS YEAR FROM Product
GROUP BY YEAR(dateOrdered),ProductId) X

Answer 5

This qualifies as a presentation concern.
Presentation and SQL don't always mix well.

Isolating your presentation logic in the application layer will:

1. save you maintenance time—change your application code, but keep your SQL intact;
2. enable you to more quickly adapt to ephemeral client requirements;
3. give you more satisfaction than fiddling with a cross-tab or pivot-table that maddeningly does almost exactly what you want.

Below is an example of how you might do this in Python (you can use the excellent pyodbc module to connect to SQL Server):

from collections import defaultdict
from datetime import date

dd = defaultdict(int)

# input 
rows = [(1,2,date(2008,10,10)), (1,1,date(2008,11,10)), 
        (1,2,date(2009,10,10)), (2,3,date(2009,10,12)), 
        (1,1,date(2009,10,15)), (2,2,date(2009,11,15))]

for row in rows:
    # row[0] == productId
    # row[1] == Qty
    # row[2] == dateOrdered
    # pyodbc enables referring to column names by name
    dd[(row[2].year, row[2].month, row[0])] += row[1]

presentation_rows = sorted(set((i[0], i[2]) for i in dd.keys()))

for i in presentation_rows:
  print i[1], i[0], 
  for j in range(0,13):
    try:
      print dd[i[0], j, i[1]], 
    except IndexError:
      print 0,
  print

# output
# 1 2008 0 0 0 0 0 0 0 0 0 0 2 1 0
# 1 2009 0 0 0 0 0 0 0 0 0 0 3 0 0
# 2 2009 0 0 0 0 0 0 0 0 0 0 3 2 0

Answer 6

You can use either a Union of your queries rather than temp tables or use the pivot option.

Here's a forum discussion on it:

Sql Server Forums - Show the row-wise data as column-wise