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
0
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);
#1
0
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);