从SQL Server中的字符串中提取月份名称和年份并对其进行透视

I want to Extract Month Name and Year from string in SQL server and pivot on them.I am having data in below format

我想从SQL服务器中的字符串中提取月份名称和年份并转向它们。我有以下格式的数据

Id   | Reason
123  |  Post-Close QC Audit - December 2015
124  |  Pre Fund Fraud Prevention
125  |  Post-Close QC Audit - November 2015
126  |  Post-Close QC Audit - October 2016
127  |  Post-Close QC Audit - November
128  |  Post-Close QC Audit - December 2015

I need two result set from this. One is all valid date and year Example:

我需要两个结果集。一个是所有有效的日期和年份示例:

REASON

December 2015
November 2015
October 2016

And second output in format

并以格式输出第二个

December 2015  |  November 2015  |  October 2016
123  |  125  |  126
128  |  NULL |  NULL

1 个解决方案

#1

Here's one way to tackle this:

这是解决这个问题的一种方法:

-- Your sample data
DECLARE @table TABLE (ID int, Reason varchar(100));
INSERT @table
VALUES
(123, 'Post-Close QC Audit - December 2015'),
(124, 'Pre Fund Fraud Prevention'),
(125, 'Post-Close QC Audit - November 2015'),
(126, 'Post-Close QC Audit - October 2016'),
(127, 'Post-Close QC Audit - November'),
(128, 'Post-Close QC Audit - December 2015');

-- solution
WITH DateExtract AS
(
  SELECT ID, Reason, Mo = 
    SUBSTRING
    (
      Reason, start, PATINDEX('% [0-9][0-9][0-9][0-9]%', SUBSTRING(Reason,start,8000))+5
    )
  FROM
  (
    SELECT 
      ID, Reason, start = NULLIF(MAX(PATINDEX('%'+Mo+'% [0-9][0-9][0-9][0-9]%', Reason)),0)
    FROM @table
    CROSS JOIN 
    ( VALUES ('January'),('February'),('March'),('April'),('May'),('June'),('July'),
             ('August'),('September'),('October'),('November'),('December')) M(Mo)
    GROUP BY ID, Reason
  ) prep
),
DateMatrix AS
(
  SELECT 
    [December 2015] = MAX(CASE Mo WHEN 'December 2015' THEN ID END),
    [November 2015] = MAX(CASE Mo WHEN 'November 2015' THEN ID END),
    [October 2015]  = MAX(CASE Mo WHEN 'October 2016'  THEN ID END)
  FROM DateExtract
  GROUP BY ID
)
SELECT * 
FROM DateMatrix
WHERE NOT([December 2015] IS NULL AND [November 2015] IS NULL AND [October 2015] IS NULL);