如何订购UNPIVOT

Question

我有以下UNPIVOT代码，我希望通过FactSheetSummary列对其进行排序，以便当它转换为行时，它是1-12顺序：

INSERT INTO #Results
SELECT DISTINCT ReportingDate, PortfolioID,ISIN, PortfolioNme, Section,REPLACE(REPLACE(Risks,'‘',''''),'’','''')
FROM  
(SELECT  DISTINCT
            ReportingDate
        ,   PortfolioID
        ,   ISIN
        ,   PortfolioNme
        ,   Section
        ,   FactSheetSummary_1, FactSheetSummary_2, FactSheetSummary_3
        ,   FactSheetSummary_4, FactSheetSummary_5, FactSheetSummary_6  
        ,   FactSheetSummary_7, FactSheetSummary_8, FactSheetSummary_9  
        ,   FactSheetSummary_10, FactSheetSummary_11, FactSheetSummary_12   
  FROM #WorkingTableFactsheet) p
UNPIVOT
(Risks FOR FactsheetSummary IN 
    (   FactSheetSummary_1, FactSheetSummary_2, FactSheetSummary_3
        ,   FactSheetSummary_4, FactSheetSummary_5, FactSheetSummary_6  
        ,   FactSheetSummary_7, FactSheetSummary_8, FactSheetSummary_9  
        ,   FactSheetSummary_10, FactSheetSummary_11, FactSheetSummary_12)
 )AS unpvt;

--DELETE records where there are no Risk Narratives
DELETE FROM #Results
WHERE Risks = ''

SELECT 
ReportingDate
,   PortfolioID
,   ISIN
,   PortfolioNme
,   Section
,   Risks   
,   ROW_NUMBER() OVER(PARTITION BY ISIN,Section ORDER BY ISIN,Section,Risks) as SortOrder
FROM #Results
order by ISIN,  Risks

有可能这样做吗？我以为UNPIVOT的情报部门会命令你？我是否需要添加一个列来规定我想要的是1到12？

Answer 1

只需用用例表达式来排序：

order by case FactsheetSummary  
          when 'FactSheetSummary_1' then 1
          when 'FactSheetSummary_2' then 2
          when 'FactSheetSummary_12' then 12 end