如何使用SQL生成自定义表 [英] how to Generate customize table using SQL

问题描述

数据库表如下:

Database Table is as follows:

Item Rev qty amount date
item1  10 10 100 2012-03-24
item2  10 10 100 2012-04-24
item3  10 10 100 2012-05-24
item4  10 10 100 2012-06-24

SELECT item as Item, SUM(rev) as Revenue, SUM(qty) as Quantity, SUM(amount) as TotalAmount FROM entry_item_new WHERE (MONTH(doe) BETWEEN ''1'' AND ''12'') and year( doe )= ''2012'' GROUP BY item

上面的查询帮助我像这样获得价值:

This above query help me to get value like this way:

Item	Revenue	   Quantity	TotalAmount
Item1	 10	      10	   100
Item1	 10	      10	   100

现在，请建议我如何以以下格式获取价值

Now please suggest me how to get value in this below format

Month	    Item1   Item2    Item3 Item4	
April,2012    10     10	       100 100	
May,2012      10     10	       100 100	

推荐答案

您将需要使用PIVOT
http://msdn.microsoft.com/en-us/library/ms177410.aspx [ ^ ]

You''ll need to use PIVOT
http://msdn.microsoft.com/en-us/library/ms177410.aspx[^]

如前所述，请阅读有关 SQL PIVOT 的信息.此处更多材料:
了解PIVOT和UNPIVOT [在SQL Server中透视数据 [

As already told, read about SQL PIVOT. More material here:
Understanding PIVOT and UNPIVOT[^]
Pivoting data in SQL Server[^]

这篇关于如何使用SQL生成自定义表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！