我之前曾发布过一个关键问题,并且已经回答了。但是,这种特殊情况需要日期,因为标题和日期将在每次生成数据透视表时更改(并且未知)。我相信我需要基于一些示例进行动态枢轴/取消枢轴,但是我无法弄清楚语法。
表格如下:
CREATE TABLE [dbo].[PhaseFlowChart](
[pfckey] [int] NULL,
[hourlykey] [bigint] NULL,
[daykey] [bigint] NULL,
[weekkey] [int] NULL,
[monthkey] [int] NULL,
[bbkey] [int] NULL,
[Day] [varchar](100) NULL,
[Date] [varchar](100) NULL,
[Bull Bear Gap] [varchar](100) NULL,
[Monthly] [varchar](100) NULL,
[Weekly] [varchar](100) NULL,
[Daily] [varchar](100) NULL,
[Hour 1] [varchar](100) NULL,
[Hour 2] [varchar](100) NULL,
[Hour 3] [varchar](100) NULL,
[Hour 4] [varchar](100) NULL,
[Hour 5] [varchar](100) NULL,
[Hour 6] [varchar](100) NULL,
[Hour 7] [varchar](100) NULL
) ON [PRIMARY]
我不需要输出中的任何“关键”列。因此,这是表格上的一个简单选择:
select [DAY],[Date],[Bull Bear Gap],[Monthly],[Weekly],[Daily],[Hour 1],[Hour 2],[Hour 3],[Hour 4],[Hour 5],[Hour 6],[Hour 7]
from PhaseFlowChart
order by pfckey asc
这是上面的输出:
我希望输出(动态枢轴/不枢轴?)看起来像这样(再次,每次生成的日期都将是不同的,这是每天的):
最终,我在网络上找到了该sql,但是经过几天的语法操作后,我仍然想不出如何移植它以满足我的需要。
CREATE TABLE #yt
([ID] int, [expense] int, [revenue] int, [date] datetime)
;
INSERT INTO #yt
([ID], [expense], [revenue], [date])
VALUES
(1, 43, 45, '2012-12-31 00:00:00'),
(2, 32, 32, '2013-01-01 00:00:00'),
(3, 64, 56, '2013-01-31 00:00:00'),
(4, 31, 32, '2013-03-03 00:00:00')
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+'_'+convert(varchar(10), #yt.date, 110))
from #yt
cross apply
(
select 'expense' col union all
select 'revenue'
) c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id,' + @cols + '
from
(
select id,
col+''_''+convert(varchar(10), date, 110) new_col,
value
from #yt
unpivot
(
value
for col in (expense, revenue)
) un
) src
pivot
(
sum(value)
for new_col in (' + @cols + ')
) p '
execute(@query);
任何/所有帮助将不胜感激。
您首先需要进入UNPIVOT
我们的专栏,然后再使用DENSE_RANK()
它为您提供一些帮助。将此结果放入临时表中,以便获得逗号分隔的DESNSE_RANK
列列表。然后创建一个UNION
so,使其Day
与Date
未透视图属于同一列。可以将其放在全局临时表中以用于动态SQL。创建一个变量,该变量将存储列列表,并构建动态SQL并执行它。
一个完整的例子(显然不要丢PhaseFlowChart
表)
-- pre-cleanup
IF OBJECT_ID('[dbo].[PhaseFlowChart]') IS NOT NULL
DROP TABLE [dbo].[PhaseFlowChart]
GO
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO
IF OBJECT_ID('tempdb..##tmp') IS NOT NULL
DROP TABLE ##tmp
GO
-- setup table and data
CREATE TABLE [dbo].[PhaseFlowChart](
[pfckey] [int] NULL,
[hourlykey] [bigint] NULL,
[daykey] [bigint] NULL,
[weekkey] [int] NULL,
[monthkey] [int] NULL,
[bbkey] [int] NULL,
[Day] [varchar](100) NULL,
[Date] [varchar](100) NULL,
[Bull Bear Gap] [varchar](100) NULL,
[Monthly] [varchar](100) NULL,
[Weekly] [varchar](100) NULL,
[Daily] [varchar](100) NULL,
[Hour 1] [varchar](100) NULL,
[Hour 2] [varchar](100) NULL,
[Hour 3] [varchar](100) NULL,
[Hour 4] [varchar](100) NULL,
[Hour 5] [varchar](100) NULL,
[Hour 6] [varchar](100) NULL,
[Hour 7] [varchar](100) NULL
) ON [PRIMARY]
INSERT INTO [dbo].PhaseFlowChart
([Day], [Date], [Bull Bear Gap], Monthly, Weekly, Daily,
[Hour 1], [Hour 2], [Hour 3], [Hour 4], [Hour 5], [Hour 6], [Hour 7])
VALUES
('MON', '20130101', 'P1', 'P1', 'P1', 'P1', 'P1', 'P1', 'P1', 'P1', 'P1', 'P1', 'P1'),
('TUE', '20130102', 'P2', 'P2', 'P2', 'P2', 'P2', 'P2', 'P2', 'P2', 'P2', 'P2', 'P2'),
('WED', '20130103', 'P3', 'P3', 'P3', 'P3', 'P3', 'P3', 'P3', 'P3', 'P3', 'P3', 'P3'),
('THU', '20130104', 'P4', 'P4', 'P4', 'P4', 'P4', 'P4', 'P4', 'P4', 'P4', 'P4', 'P4'),
('FRI', '20130105', 'P5', 'P5', 'P5', 'P5', 'P5', 'P5', 'P5', 'P5', 'P5', 'P5', 'P5'),
('SAT', '20130106', 'P6', 'P6', 'P6', 'P6', 'P6', 'P6', 'P6', 'P6', 'P6', 'P6', 'P6'),
('SUN', '20130107', 'P7', 'P7', 'P7', 'P7', 'P7', 'P7', 'P7', 'P7', 'P7', 'P7', 'P7')
GO
-- unpivot the columns into 'categories'
SELECT [Day], [Date], [Value], [Category],
DENSE_RANK() OVER (ORDER BY CAST([Date] AS DATE)) dr
INTO #tmp
FROM PhaseFlowChart pfc
UNPIVOT (
Value FOR Category IN ([Bull Bear Gap], Monthly, Weekly, Daily,
[Hour 1], [Hour 2], [Hour 3], [Hour 4],
[Hour 5], [Hour 6], [Hour 7])
) upiv
-- create a global temp table for use later
SELECT *
INTO ##tmp
FROM (
-- union data into single category column
SELECT 'Day' Category, [Day] Value, dr, 1 o FROM #tmp
UNION ALL
SELECT 'Date' Category, [Date] Value, dr, 2 o FROM #tmp
UNION ALL
SELECT [Category], Value, dr, 3 o FROM #tmp
) t
-- get a comma seperated list of columns for the PIVOT
DECLARE @cols VARCHAR(MAX) =
STUFF(CAST((SELECT ',' + QUOTENAME(dr)
FROM (
SELECT DISTINCT dr
FROM #tmp
) t
ORDER BY dr
FOR XML PATH(''), TYPE
) AS VARCHAR(MAX)),1,1,'')
-- create and execute the sql
DECLARE @sql VARCHAR(MAX) = '
SELECT Category, ' + @cols + '
FROM ##tmp
PIVOT (
MAX([Value])
FOR dr IN (' + @cols + ')
) piv
ORDER BY o, CASE Category
WHEN ''Daily'' THEN 4
WHEN ''Weekly'' THEN 3
WHEN ''Monthly'' THEN 2
WHEN ''Bull Bear Gap'' THEN 1
ELSE 5 END, Category
'
EXEC(@sql)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句