Geek Answers Handbook

How can I make month columns in Sql?

I have a dataset 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

From this, we are 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

As I do this now, I make 12 samples, one for each month, and put them in temp tables. Then I make a giant mix. Everything works, but this guy is a dog slowly.

I know that this is not so much, but knowing that I barely qualify as a dash in the db world, I wonder if there is a higher level approach to this that I could try. (I guess there is.)

(I use MS Sql Server, so the answers specific to this database are fine.)

(I’m just starting to look at PIVOT as a possible help, but I don’t know anything about it yet, so if someone wants to comment on this, it can be useful too.)

+6
sql-server
source share
7 answers
 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

+9
source share

You can check out the following article:

+2
source share

You can use either the union of your queries, not temporary tables, or use the pivot option.

The discussion on the forum is discussed here:

Sql Server Forums - Show string data by columns

+1
source share
 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
+1
source share

It is heavy ...

One of the built-in functions for converting rows / data into columns is the PIVOT function on the MS SQL server.

I have no specifics, it is only from the head.

0
source share

This relates to the problem of presentation.
Presentation and SQL do not always mix well.

Isolation of presentation logic at the application level will be:

  • save maintenance time - change application code, but keep your SQL intact;
  • allow you to quickly adapt to the requirements of ephemeral clients;
  • gives you more satisfaction than messing around with a crosstab or pivot table that madly does almost what you want.

Below is an example of how you can 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
0
source share

try it. Thus, this code will select data in a specific time range and then convert it to a new column. For example, in my sql code: it selects the time interval between "2014-10-01" and "2014-10-31" from the column "L_dt", and then creates a new column "October". Thus, we can lay out the data in different columns coming 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 this: L_dt
2014-10-13 2014-12-21 2014-11-22 2014-10-10

Then the conclusion will be October November December 2 1 1

-one
source share

More articles:


All Articles