使用数据透视功能如何修改表数据 [英] Using Pivot function how to modify Table data

问题描述

表结构为

Item_Details

Item	Unit	Date	Amount
item1	Kg	2012-03-24	25
item2	Ltr	2012-03-24	25
item3	Kg	2012-03-24	25
item1	Kg	2012-03-25	25
item1	Kg	2012 -03-26	25
item2	Kg	2012-03-27	25
item3	Kg	2012-03-28	25

并使用数据透视功能，我想以如下方式显示数据:

Month	item1 (Kg)	item2 (Ltr)	item3 (Kg)
Jan	0	0	0
二月	0	0	0
Mar	75	50	50

目前，我已经编写了这样的数据透视功能

Table structure is

Item_Details

Item	Unit	Date	Amount
item1	Kg	2012-03-24	25
item2	Ltr	2012-03-24	25
item3	Kg	2012-03-24	25
item1	Kg	2012-03-25	25
item1	Kg	2012-03-26	25
item2	Kg	2012-03-27	25
item3	Kg	2012-03-28	25

And using Pivot function i want to display data like the following way:

Month	item1 (Kg)	item2 (Ltr)	item3 (Kg)
Jan	0	0	0
Feb	0	0	0
Mar	75	50	50

presently i have written pivot function like this

SELECT  (MONTH(date)),[item1]
      , [item2]
      , [item3]
FROM    ( SELECT    date,item
                  , amount
          FROM      item_details
        ) p PIVOT ( SUM(amount)
                    FOR item
                      IN ([item1],[item2],[item3])
                  ) AS pvt

我有:

	item1	item2	item3
3	25	25	25
3	25	0	0
3	25	0	0
3	0	25	0
3	0	0	25

and i have got:

	item1	item2	item3
3	25	25	25
3	25	0	0
3	25	0	0
3	0	25	0
3	0	0	25

推荐答案

如果要获取月份的名称，则需要这样编写SQL查询(使用

If you would like to get the name of month, you need to write SQL query like this (using DATENAME[^] function):

SELECT DATENAME(mm, '2011-05-19') AS [MonthName]
UNION ALL
SELECT DATENAME(mm, '2012-03-01') AS [MonthName]
UNION ALL
SELECT DATENAME(mm, '2012-02-29') AS [MonthName]
UNION ALL
SELECT DATENAME(mm, '2012-06-30') AS [MonthName]

...但是看看结果:

MonthName
May
3月
二月
六月

日期-年-的部分丢失；(
在开始编程SQL查询之前，您需要记住它.

您应该记住的第二件事是:列的名称不应与系统对象或函数的名称相同！
更改Item_details表中date列的名称，例如DateOfSomething

让我们来做这件事:

...but take a look at results:

MonthName
May
March
February
June

The part of date - year - is missing ;(
You need to remember it, before we start programming SQL query.

The second thing you should to remember is: The columns shouldn''t have the same names as system objects or functions!
Change the name for date column in Item_details table, like this DateOfSomething

Let''s do the job:

--declare variables
DECLARE @cols NVARCHAR(100)
DECLARE @dtt NVARCHAR(1000)
DECLARE @pvt NVARCHAR(1000)

--set variables
--get columns
SET @cols = STUFF((SELECT DISTINCT '],[' + CONVERT(NVARCHAR, ItDs.[category] + '_' + ItDs.[unit]) AS [Item]
	FROM [Item_Details] AS ItDs
	ORDER BY '],[' + CONVERT(NVARCHAR,ItDs.[category] + '_' + ItDs.[unit])
	FOR XML PATH('')),1,2,'') + ']'
--EXEC @cols
--results: [A_Kg],[B_Ltr],[C_Kg]

--data table, the source of pivot table
SET @dtt = 'SELECT DATENAME(mm, ItDs.[date]) AS [MonthOfDate], CONVERT(NVARCHAR, ItDs.[category] + '_' + ItDs.[unit]) AS [Item], ItDs.[amount] ' +
'FROM item_details AS ItDs ' +
'WHERE YEAR(ItDs.[date])= 2012'
--EXEC(@dtt)
--result columns for year 2012: [MonthOfDate],[Item],[amount] 
--where [Item] values are exactly the same as the values in @cols variable

SET @pvt = 'SELECT [MonthOfDate], ' + @cols + ' ' +
              'FROM (' + @dtt + ') AS DT ' +
         'PIVOT ( SUM([amount]) FOR [item] IN (' + @cols + ') ) AS PT ' 
EXEC(@pvt)

结果:

MonthOfDate	A_Kg	B_Ltr	C_Kg
三月	25	25	25
三月	25	0	0
三月	25	0	0
三月	0	25	0
三月	0	0	25

*********************************************** *** [/EDIT]
如果表的结构是:

id	项目	日期	金额
1	item1	2012-03-24	25
1	item2	2012-03-24	25
1	item3	2012-03-25	25
1	item1	2012-03-26	25
1	item1	2012 -03-27	25
1	item2	2012-03-28	25

并且您想要显示这样的数据:

Month	Item1	Item2	Item3
Jan	0	0	0
2月	0	0	0
Mar	75	50	50

results:

MonthOfDate	A_Kg	B_Ltr	C_Kg
March	25	25	25
March	25	0	0
March	25	0	0
March	0	25	0
March	0	0	25

**************************************************[/EDIT]
If the structure of table is:

id	item	date	amount
1	item1	2012-03-24	25
1	item2	2012-03-24	25
1	item3	2012-03-25	25
1	item1	2012-03-26	25
1	item1	2012-03-27	25
1	item2	2012-03-28	25

and you want to display data like this:

Month	Item1	Item2	Item3
Jan	0	0	0
Feb	0	0	0
Mar	75	50	50

--declare variables
DECLARE @cols NVARCHAR(100)
DECLARE @dtt NVARCHAR(1000)
DECLARE @pvt NVARCHAR(1000)

--set variables
--get columns
SET @cols = STUFF((SELECT DISTINCT '],[' + ItDs.[Item] 
	FROM [Item_Details] AS ItDs
	ORDER BY '],[' + ItDs.[Item]
	FOR XML PATH('')),1,2,'') + ']'
--EXEC @cols
--results: [item1],[item2],[item3]

--data table, the source of pivot table
SET @dtt = 'SELECT DATENAME(mm, ItDs.[date]) AS [Month], ItDs.[Item], ItDs.[amount] ' +
'FROM item_details AS ItDs ' +
'WHERE YEAR(ItDs.[date])= 2012'
--EXEC(@dtt)

SET @pvt = 'SELECT [Month], ' + @cols + ' ' +
              'FROM (' + @dtt + ') AS DT ' +
         'PIVOT ( SUM([amount]) FOR [item] IN (' + @cols + ') ) AS PT ' 
EXEC(@pvt)

这篇关于使用数据透视功能如何修改表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！