用Server汇总到财政年度的月份列
用Server汇总到财政年度的月份列
提问于 2016-01-18 23:25:38
我想总结一张表，它主要是由几个月组成的。每个月的列都有一个MMMYY格式的标题。我需要按财政年度动态汇总此表。我们的财政年度从11月开始，因此对产出的投入如下：
输入：
输出：
有人对我如何使用Server来做这件事有任何建议吗？我考虑过在每月财政年度间隔(NovYY)之前划分一个包含所有给定月份和年份的临时表，但我还没有弄清楚如何基于LIKE关键字搜索进行分区。底线-我完全不知道如何有效地做到这一点，而不硬编码的日期。
假设上面的示例输入是dbo.USA_Historic_Txg_Revenue。到目前为止，我有以下几点：
select * into #temp from dbo.USA_Historic_Txg_Revenue
with MMMYY as
(
select Name
from tempdb.sys.columns
where object_id = OBJECT_ID('tempdb..#temp')
and isnumeric(right(name, 2)) = 1
)
select * from MMMYY 回答 2
Stack Overflow用户
回答已采纳
发布于 2016-01-19 01:07:03
这就是我认为你要找的东西：
--CREATE TABLE #table (Name NVARCHAR(100), Oct12 INT, Nov12 INT, Dec12 INT, Jan13 INT, Feb13 INT, Mar13 INT, Apr13 INT, May13 INT, Jun13 INT, Jul13 INT, Aug13 INT, Sep13 INT, Oct13 INT, Nov13 INT, Dec13 INT, Jan14 INT, Feb14 INT, Mar14 INT)
--INSERT #table VALUES ('Record1',5,3,2,0,1,5,4,3,6,5,4,2,1,0,1,1,1,2)
--, ('Record2',4,1,3,2,1,3,3,1,4,4,3,2,1,0,2,2,2,2);
DECLARE @monthlist NVARCHAR(MAX)
SELECT @monthlist = ISNULL(@monthlist + ',', '') + Name -- select top 100 *
FROM tempdb.sys.columns
WHERE [object_id] = OBJECT_ID('tempdb..#table')
AND Name <> 'Name';
DECLARE @SQL NVARCHAR(MAX) =
'SELECT DISTINCT Name
, SUM(Val) OVER (PARTITION BY Name, CASE WHEN Mth LIKE ''Nov%'' OR Mth LIKE ''Dec%'' THEN RIGHT(Mth, 2) ELSE RIGHT(Mth, 2) - 1 END) InsertColName
, CASE WHEN Mth LIKE ''Nov%'' OR Mth LIKE ''Dec%'' THEN ''Nov'' + RIGHT(Mth, 2) ELSE ''Nov'' + CONVERT(NVARCHAR, RIGHT(Mth, 2) - 1) END FiscalStart
FROM #table
UNPIVOT (Val FOR Mth IN (' + @monthlist + ')) a
ORDER BY Name, CASE WHEN Mth LIKE ''Nov%'' OR Mth LIKE ''Dec%'' THEN ''Nov'' + RIGHT(Mth, 2) ELSE ''Nov'' + CONVERT(NVARCHAR, RIGHT(Mth, 2) - 1) END'
EXEC(@SQL)对于特定的查询，您可以直接从表中派生月份列表，而不需要临时表.
例如：
DECLARE @monthlist NVARCHAR(MAX)
SELECT @monthlist = ISNULL(@monthlist + ',', '') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'USA_Historic_Txg_Revenue'
AND COLUMN_NAME <> 'Name';
DECLARE @SQL NVARCHAR(MAX) =
'SELECT DISTINCT Name
, SUM(Val) OVER (PARTITION BY Name, CASE WHEN Mth LIKE ''Nov%'' OR Mth LIKE ''Dec%'' THEN RIGHT(Mth, 2) ELSE RIGHT(Mth, 2) - 1 END) InsertColName
, CASE WHEN Mth LIKE ''Nov%'' OR Mth LIKE ''Dec%'' THEN ''Nov'' + RIGHT(Mth, 2) ELSE ''Nov'' + CONVERT(NVARCHAR, RIGHT(Mth, 2) - 1) END FiscalStart
FROM dbo.USA_Historic_Txg_Revenue
UNPIVOT (Val FOR Mth IN (' + @monthlist + ')) a
ORDER BY Name, CASE WHEN Mth LIKE ''Nov%'' OR Mth LIKE ''Dec%'' THEN ''Nov'' + RIGHT(Mth, 2) ELSE ''Nov'' + CONVERT(NVARCHAR, RIGHT(Mth, 2) - 1) END'
EXEC(@SQL)Stack Overflow用户
发布于 2016-01-19 02:24:09
使用枢轴汇总月份列
---creating the tables
CREATE TABLE [dbo].[Table1]([PID] [int] NULL,[ProductDesc] [nvarchar](50) NULL,[ProductCode] [nvarchar](10) NULL) ON [PRIMARY]
CREATE TABLE [dbo].[Table2]([Date] [varchar](50) NULL,[PID] [int] NULL) ON [PRIMARY]
---insert script---
INSERT [dbo].[Table1] ([PID], [ProductDesc], [ProductCode]) VALUES (1, N'Packet-Eye', N'P001')
INSERT [dbo].[Table1] ([PID], [ProductDesc], [ProductCode]) VALUES (2, N'Wiggy', N'W099 ')
INSERT [dbo].[Table1] ([PID], [ProductDesc], [ProductCode]) VALUES (3, N'Wimax-Lite', N'W001')
INSERT [dbo].[Table1] ([PID], [ProductDesc], [ProductCode]) VALUES (4, N'Wimax-Home', N'e W002 ')
INSERT [dbo].[Table2] ([Date], [PID]) VALUES (N'1/14/2009 ', 1)
INSERT [dbo].[Table2] ([Date], [PID]) VALUES (N'1/15/2009 ', 1)
INSERT [dbo].[Table2] ([Date], [PID]) VALUES (N'2/1/2009', 2)
INSERT [dbo].[Table2] ([Date], [PID]) VALUES (N'3/3/2009', 3)
GO
SELECT *
FROM
(
SELECT t1.productdesc as pd,COUNT(month(t2.date))as dates,
DateName( month , DateAdd( month , MONTH(t2.date) , 0 ) - 1 ) as mon
FROM table1 t1 inner join table2 t2 on t1.pid=t2.pid
where year(date) between 2009
and 2010 group by productdesc,month(t2.date),month (t2.date)
) AS D
PIVOT
(
sum(dates)
FOR mon IN( [January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])
) AS P
[using pivot in sqlserver please find the result in the below image][1]
[1]: http://i.stack.imgur.com/jio6c.png页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/34865969
复制相关文章
相似问题
腾讯云开发者
