SSRS和字符串解析以生成报告

Question

一位朋友告诉我，他的新雇主需要一份SSRS报告，它可以解析包含n个连续出现的列: 1)文字" date :“2)一个可选的分隔符字符3)后面跟着一个DD-MM-YY格式的日期(前导零是可选的) 4)一个分隔符空格5)一个与该日期相关的数据的”单词“。这个单词不会有嵌入的空格。

我将使用符合此标准的数据填充一个示例表，并给出一个示例来说明这一点：

CREATE TABLE [dbo].[Sample](
    [RowNumber] [int] NOT NULL,
    [DataMess] [varchar](max) NOT NULL
) ON [PRIMARY]


INSERT [dbo].[Sample] ([RowNumber], [DataMess]) VALUES (1, N'Date:12-21-13 12/13/14/15 Date:4-2-11 39/12/134/14 Date:4-1-13 19/45/5/12')
INSERT [dbo].[Sample] ([RowNumber], [DataMess]) VALUES (2, N'Date:7-21-13 12/13/14/15 Date:8-21-12 39/12/34/14 Date:12-1-13 19/4/65/12')
INSERT [dbo].[Sample] ([RowNumber], [DataMess]) VALUES (3, N'Date:3-21-13 12/11233/14/15 Date:4-28-13 39/12/34/14 Date:9-19-13 19/45/65/12')

对于第一条记录，"12/13/14/15“被认为是与日期12-21-13相关联的数据的”单词“。

他被要求在SSRS中生成以下报告：

Row Number               DataMess
1                        Date: 12-21-13 12/13/14/15 
                         Date: 4-1-13 19/45/5/12 
                         Date: 4-2-11 39/12/134/14 

2                        Date:12-1-13 19/4/65/12 
                         Date:7-21-13 12/13/14/15 
                         Date:8-21-12 39/12/34/14 

3                        Date:9-19-13 19/45/65/12 
                         Date:4-28-13 39/12/34/14 
                         Date:3-21-13 12/11233/14/15  

请注意，每个源行号的日期按照与数据的关联wor降序排列。

我不知道SSRS，但我的反应是建议他不要尝试这项任务，而是告诉他的雇主，数据真的不应该尝试用T-SQL解析所有那些丑陋的字符串。相反，这个重复的"Date: DATA“应该存储在与父行记录相关联的各个子记录中。我相信代码将是丑陋的，低效的，脆弱的和难以维护的。你的想法是什么？

假设management\client总是正确的，或者承认“理想情况下”这是正确的，但是“现在”我们需要一个SQL来生成下面的报告，该怎么做呢？人们的期望是这可以很快地完成(例如，半天)

Answer 1

你当然是对的，这肯定不是存储数据的最佳方式。与以不同的方式存储数据相比，提取此报告的数据的任何方法都要复杂得多。

然而，根据数据，实际生成报告仍然不会太困难。由于表结构的原因，为报告实际生成数据集将是最困难的部分。

因此，要生成数据集，您需要在DataMess中拆分数据，以获得每个日期/单词一行，并能够从拆分的数据中提取日期，以便能够根据需要按日期排序。

您可以选择如何拆分数据：

Split function equivalent in T-SQL?有很多选项，链接- Best Split Function也是如此。

这是一个运行了其中一个函数的SQL Fiddle。

拆分数据后，使用适当的函数提取日期部分，即在冒号和单词data之前的空格之间，然后将其CAST为日期。

一旦你实际获得了数据集，它就是最简单的报告了--只需添加一个基于RowNumber的行组，将拆分的日期/单词数据添加为详细信息字段，就完成了。确保数据集是按提取的日期字段排序的，即使您实际上没有在报告中显示它。

作为一项临时措施，我肯定希望在半天左右的工作时间内就能做到这一点。因此，仅对于这份报告来说，这并不是太糟糕，但对于其他任何情况，您可能会遇到麻烦。

对于一些行，它可能运行得很好，但在任何非平凡大小的数据集上，性能都不是最优的。

Answer 2

谢谢。下面是我对其余部分所做的，以便按DESC顺序对日期进行排序。

SELECT
     RowNumber
     ,'Date: ' + ss.Item AS Data
     --,cast(substring(ss.Item, 1, charindex(' ' , ss.Item) ) AS date)
FROM
     Sample s
     CROSS apply dbo.SplitStrings_XML(s.DataMess,
                                      N'Date:') ss
WHERE
     Item IS NOT NULL
ORDER BY
     rownumber, 
     cast(substring(ss.Item, 1, charindex(' ' , ss.Item) ) AS date) desc

如果数据不能保持预期的格式，并且我们遇到一个无效的日期，那么整个报告就会崩溃。