如何在Sql中创建“月”列？

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.

我现在这样做的方式,我正在做12个选择,每个月一个,并把它们放在临时表中。然后我做了一个巨大的加入。一切正常,但这家伙是狗慢。

I know this isn't much to go on, but knowing that I barely qualify as a tyro in the db world, I'm wondering if there is a better high level approach to this that I might try. (I'm guessing there is.)

我知道这并不多,但我知道我几乎没有资格成为db世界中的一个tyro,我想知道是否有一个更好的高级方法,我可能会尝试。 (我猜是有的。)

(I'm using MS Sql Server, so answers that are specific to that DB are fine.)

(我正在使用MS Sql Server,因此特定于该数据库的答案很好。)

(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 about that, that might be helpful as well.)

(我刚刚开始将“PIVOT”视为一种可能的帮助,但我对此一无所知,所以如果有人想对此发表评论,那也可能有所帮助。)

7 个解决方案

#1

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

#2

You may want to check the following article:

您可能需要查看以下文章:

• Creating cross tab queries and pivot tables in SQL Server

在SQL Server中创建交叉表查询和数据透视表

#3

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

Sql Server论坛 - 以列方式显示行方式数据

#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

#5

That's a tough one ...

那是一个艰难的...

One of the built-in functions to convert rows/data to columns is the PIVOT function in MS SQL server.

将行/数据转换为列的内置函数之一是MS SQL服务器中的PIVOT函数。

I don't have the specifics, this is just from the top of my head.

我没有具体细节,这只是我的头脑。

#6

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

这有资格作为演示问题。演示文稿和SQL并不总是很好地混合。

Isolating your presentation logic in the application layer will:

在应用程序层中隔离表示逻辑将:

1. save you maintenance time—change your application code, but keep your SQL intact;

节省您的维护时间 - 更改您的应用程序代码,但保持您的SQL完整;

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):

下面是如何在Python中执行此操作的示例(您可以使用优秀的pyodbc模块连接到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

#7

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.

尝试这个。因此,此代码将在特定时间范围内选择数据,然后将其转换为新列。例如,在我的sql代码中:它从“L_dt”列中选择“2014-10-01”和“2014-10-31”之间的时间范围,然后创建一个名为“October”的新列。通过这种方式,我们可以在源自一列的不同列中布置数据。

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

如果输入如下: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     |
+---------+----------+----------+