将选择结果(一列)分成多列 [英] split select results (one column) into multiple columns

问题描述

我一直在尝试解决该问题的方法，但是我想我只是不具备一开始就知道该寻找什么的技能.我正在使用现有系统，因此无法更改数据库架构，也不能指示用户如何输入数据.我必须与我们所拥有的一起工作.

I've been trying to work out how to accomplish this, but I think I just don't have the skills to know what to look for in the first place. I'm working with an existing system, and I cannot alter the database schema, nor can I dictate how the users enter data. I have to work with what we have.

当前，我们的用户正在将统计数据放入表格的一个文本字段中.他们使用每行一个统计信息的标准格式，并使用文本限定符来界定"统计信息的详细信息.即

Currently, our user is putting stats data into one text field in a table. They are using a standard format of one stat per line, with text qualifiers to 'delimit' the details of the stat. i.e.

<Category> - D:<Description> Q:<Quanitity> V:<Value>
<Category> - D:<Description> Q:<Quanitity> V:<Value>

(实际数据中没有任何<>括号……我只是用它们来显示详细信息的位置.)

(there aren't any <> brackets in the actual data... I was just using them to show where details go.)

在上面的示例中，两个统计信息保存在一个文本字段记录中...并且表中有许多统计"记录.

In the above example, the two stats are held in one text field record... and there are many of these 'stats' records in the table.

我正在使用MS SQL Server 2005 我需要为每个描述创建数量/值总和的报告.

I am using MS SQL Server 2005 I need to create a report for sums of quanitites/values per description.

我实现了一个拆分用户功能，可以在一条记录上使用该功能将每一行拆分为单独的记录...但是据我所能达到的范围.

I have implemented a split user function that I can use on one record to split each line into individual records... but that is as far as I've managed to get.

我需要从StatsTable中选择Stats"，然后遍历每个stats记录，将其拆分成单独的行，然后从每个拆分行中提取类别，描述，数量和值，然后将所有结果返回为一个桌子.

I need to 'Select Stats from StatsTable', then loop through each stats record, split it up into individual lines, then extract the category, description, quantity and value from each split line, then return all of the results in one table.

推荐答案

我设法将一个嵌套的游标修补在一起...看起来很有效.

I've managed to patch together a nested cursor... It looks like it works.

declare o CURSOR FAST_FORWARD FOR
select comments from EVENT

declare @comment nvarchar(max)

OPEN o FETCH NEXT FROM o into @comment

while @@FETCH_STATUS = 0
BEGIN
Declare @item nvarchar(750)

declare @tbl Table(Category nvarchar(250), Description nvarchar(250), Quantity nvarchar(250), Value DECIMAL(10,2))

declare c CURSOR FAST_FORWARD FOR
SELECT items FROM dbo.Split(@comment, Char(10))

OPEN c FETCH NEXT FROM c into @item

WHILE @@FETCH_STATUS = 0
BEGIN
    set @item = @item + ':'
    insert into @tbl
    Values (LTRIM(RTRIM(SUBSTRING(@item, 1, CHARINDEX(' - ', @item) - 1))),
            CASE when @item like '%D:%' Then LTRIM(RTRIM(SUBSTRING(@item, CHARINDEX('D:', @item) + 2, CHARINDEX(':', @item, CHARINDEX('D:', @item)+2) - CHARINDEX('D:', @item) - 3))) else '' end,
            CASE when @item like '%Q:%' Then LTRIM(RTRIM(SUBSTRING(@item, CHARINDEX('Q:', @item) + 2, CHARINDEX(':', @item, CHARINDEX('Q:', @item)+2) - CHARINDEX('Q:', @item) - 3))) else '1' end,
            CASE when @item like '%V:%' Then CONVERT(DECIMAL(10,2),dbo.RemoveNonNumericCharacters(LTRIM(RTRIM(SUBSTRING(@item, CHARINDEX('V:', @item) + 2, CHARINDEX(':', @item, CHARINDEX('V:', @item)+2) - CHARINDEX('V:', @item) - 3))))) else 0 end)
    FETCH NEXT FROM c into @item
END
CLOSE c DEALLOCATE c
END
CLOSE o DEALLOCATE o
SELECT * FROM @tbl

这篇关于将选择结果(一列)分成多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！