将一列拆分为多列但数据会有所不同 SQL [英] Split one column to multiple columns but data will vary SQL

问题描述

我的数据库中有一组如下所示的行:

I have a set of row in my database that look like this:

Mr Joe Bloggs
Joe Bloggs
Joe Jim Bloggs
Joe Jim David Bloggs
Mr Joe Jim Bloggs
Mr Joe Jim David Bloggs

需要输出:

Title | First Name | Middle Name | Surname
------------------------------------------
Mr | Joe | x | Bloggs
x | Joe | x | Bloggs
x | Joe | Jim | Bloggs
x | Joe | Jim David | Bloggs
Mr | Joe | Jim | Bloggs
Mr | Joe | Jim David | Bloggs

这将在 SQL 存储过程中运行.我需要的是在循环遍历 CURSOR 和 4 个变量以更新数据库中的 4 列时生成的输出.但正如您所看到的，数据在 1 列中并且各不相同.

This will be run in a SQL Stored Procedure. What i need is this output generated while looping through a CURSOR and the 4 variables to update the 4 columns in the database. But as you can see the data is in 1 column and varies.

我发现很难理解执行此操作的最佳方法，并希望得到您的帮助.如果您需要其他任何东西，请告诉我.

I am finding it hard to understand the best way of doing this and would like your help please. Let me know if you need anything else.

与我的类似问题，但我觉得这些问题没有给我所需的完整答案，或者我认为它没有回答我的问题:

Similar Questions to mine but i feel these dont give me the full answer i need or i think it doesnt answer my question:

• 将列字符串拆分为多列字符串
• 将选择结果(一列)拆分为多列
• SQL 单列拆分成多列
• 如何将列值拆分为SQL 中的不同列
• 将一列拆分为两列的 Sql 查询
• 表列在sql中拆分为两列?

    --Temporary Table to Merge the full name together in order to split by comma. Each split will have a unique record but with the same address information for each name. 
    CREATE TABLE #TempOutput
    (
        ClientReference varchar(MAX),
        Prefix varchar(MAX),
        Reference int,
        MergedFullName varchar(MAX),
        FormatName varchar(MAX),
        Title varchar(MAX),
        FirstName varchar(MAX),
        MiddleName varchar(MAX),
        Surname varchar(MAX)
    )

    DECLARE @Ref varchar(MAX)
    DECLARE @Prefix varchar(MAX)
    DECLARE @Reference int
    DECLARE @MergedName varchar(MAX)
    DECLARE @FormatName varchar(MAX)
    DECLARE @Title varchar(MAX)
    DECLARE @FirstName varchar(MAX)
    DECLARE @MiddleName varchar(MAX)
    DECLARE @Surname varchar(MAX)

    DECLARE ExtraNameSplitCursor CURSOR FOR
        SELECT 
            [Client Reference], 
            Prefix,
            Reference,
            CASE 
                WHEN LEFT(Surname,3) = 'Mr,' THEN (Title + ' ' + [First Name] + ' ' + [Middle Name] + ' ' + Replace(Surname,'Mr,',''))
                WHEN LEFT(Surname,4) = 'Mrs,' THEN (Title + ' ' + [First Name] + ' ' + [Middle Name] + ' ' + Replace(Surname,'Mrs,',''))
                ELSE (Title + ' ' + [First Name] + ' ' + [Middle Name] + ' ' + Surname) 
            END AS [Merged Full Name]
        FROM #StartResults

    OPEN ExtraNameSplitCursor

    FETCH NEXT FROM ExtraNameSplitCursor INTO @Ref, @Prefix, @Reference, @MergedName
    WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT INTO #TempOutput (ClientReference, Prefix, Reference, MergedFullName) 
            SELECT @Ref, @Prefix, @Reference, GUID, FROM dbo.fn_SPLIT_LIST_STRING (@MergedName)

            --SELECT CASE 
            --  WHEN @FormatName.GUID = 'Mr ' THEN @Title = 'Mr'
            --  WHEN LEFT(@MergedName,4) = 'Mrs ' THEN @Title = 'Mrs' 
            --  WHEN LEFT(@MergedName,5) = 'Miss ' THEN @Title = 'Miss'
            --  WHEN LEFT(@MergedName,3) = 'Ms ' THEN @Title = 'Ms'
            --  WHEN LEFT(@MergedName,3) = 'Dr ' THEN @Title = 'Dr'
            --  WHEN LEFT(@MergedName,4) = 'Rev ' THEN @Title = 'Rev'
            --  WHEN LEFT(@MergedName,5) = 'Lord ' THEN @Title = 'Lord'
            --  ELSE @Title = '' 
            --END AS Title,
            --CASE 
            --  WHEN MID(@MergedName,2,1) = ' ' THEN @FirstName = '' ELSE @FirstName = CHARINDEX(' ', MergedFullName + ' ') FROM #TempOutput AS FirstName

            FETCH NEXT FROM ExtraNameSplitCursor INTO @Ref, @Prefix, @Reference, @MergedName, @FlatNo, @HouseName, @HouseNo, @Street, @District, @PostTown, @County, @PostCode
        END

    CLOSE ExtraNameSplitCursor
    DEALLOCATE ExtraNameSplitCursor

    --Select statement for temporary table
    SELECT 
        ISNULL(ClientReference,'') AS ClientReference,
        ISNULL(Prefix,'') AS Prefix,
        ISNULL(Reference,'') AS Reference,
        ISNULL(MergedFullName,'') AS MergedFullName, 
        ISNULL(Title,'') AS Title,
        ISNULL(FirstName,'') AS FirstName,
        ISNULL(MiddleName,'') AS MiddleName,
        ISNULL(Surname,'') AS Surname

    FROM #TempOutput Result
    ORDER BY ClientReference
    for xml auto

    --Drop all temporary tables
    DROP TABLE #TempOutput
    DROP TABLE #StartResults

END

推荐答案

我已经解决了我的问题，感谢提供线索和帮助.

I have resolved my issue and thanks for the clues and help.

我基本上添加了第二个光标，它执行以下操作:

I have basically added a 2nd cursor which does the following:

• 获取合并的全名字段并放入一个临时变量中.
• 通过运行案例语句来检查先生、女士等来检查标题.否则在标题中输入空字符串.
• 更新标题修剪合并全名的标题
• 通过 RIGHT(@TempName, CHARINDEX(' ',REVERSE(@TempName)) - 1) 并假设这是姓氏.
• 更新姓氏修剪并替换合并的全名
• 通过 LEFT(@TempName, CHARINDEX(' ',
  @TempName + ' ') -1)
• 更新名字修剪并替换合并的全名
• 其他任何内容都放在中间名中.

有点啰嗦，但在我的数据库中 99.9% 的情况下，它可以完美地格式化.

Bit long winded but in 99.9% cases in my database it formats pefectly.

如果你想要上面的代码，请告诉我.

If you would like the above code let me know.

感谢您抽出宝贵时间.

这篇关于将一列拆分为多列但数据会有所不同 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！