您首先需要进入UNPIVOT我们的专栏，然后再使用DENSE_RANK()它为您提供一些帮助。将此结果放入临时表中，以便获得逗号分隔的DESNSE_RANK列列表。然后创建一个UNIONso，使其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)
602392714
清零编程群