如何迭代SQL结果值中的字符

Question

我们有一个包含ID和标题的作业表，类似于以下内容：

JobID | JobTitle
1       president
2       vice-president
3       director

用户表包括一个jobID，该jobID本应映射到作业表，但无论是谁对其进行编码，都会使其成为UI中的多选字段，并在其之间添加带有管道的值。因此，用户可以是总裁、副总裁和董事。以下是用户表的示例

UserName | JobID
Suzy       1|2|3
Bob        3
Jane       2|1

我正试图运行一个关于所有员工及其职务的报告，但对于如何迭代多值jobID并显示jobTitle感到困惑。

我目前使用的查询类似于：

select user.username, job.JobTitle 
from user 
inner join job on user.JobID = job.JobID

这一切都在SQL Server 2012 上

有什么建议吗？我愿意在后续行或后续列中显示额外的标题，只要更容易。

Answer 1

这里有一个函数，可以用来分割分隔字段并将结果返回到表（然后可以在后续操作中使用）：

CREATE FUNCTION dbo.ufnGENSplitDelimField (
    @InputString nvarchar(max),
    @Delimiter nvarchar(10)
)
RETURNS @Results TABLE (
    Item nvarchar(50)
)
AS
BEGIN
-- default delimiter to comma if blank
IF ISNULL(@Delimiter,'') = ''
BEGIN
    SET @Delimiter = ','
END
DECLARE @Item nvarchar(50);
DECLARE @ItemList nvarchar(max);
DECLARE @DelimIndex int;
SET @ItemList = @InputString
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
/*
Loop through all items, removing the first at each iteration.  For example, if we start with the string 'bob,mary,joe':
    1. set @Item = 'bob'
    2. insert 'bob' into result table
    3. set @ItemList = 'mary,joe'
    4. set @DelimIndex = 4
    loop
*/
WHILE (@DelimIndex != 0)
BEGIN
    SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
    INSERT INTO @Results VALUES (@Item)
    -- Set @ItemList = @ItemList minus one less item
    SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
    SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
END -- End WHILE
IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString, and we need to insert the final item in the list
BEGIN
    SET @Item = @ItemList
    INSERT INTO @Results VALUES (@Item)
END
ELSE -- No delimiters were encountered in @InputString, so just return @InputString
BEGIN
    INSERT INTO @Results VALUES (@InputString)
END
RETURN;
END;

Answer 2

最好的答案显然是简单地拆分列，并创建一个单独的表，每个子级（作业）有一行，以拥有原子数据。但与此同时，你可以做一些类似的事情：

SELECT [user].username, [job].JobTitle
FROM [job]
INNER JOIN [user] 
    ON ('|'+[user].JobID+'|' LIKE('%|'+CAST([job].JobID as varchar(20))+'|%'))

哦，在你对某人在表中插入非原子数据大喊大叫之前，首先要避开他，因为他使用了保留关键字作为表名。从不从不从不从不将表命名为user。

---

顺便说一句，这个语法可以用来做一些类似的事情：

CREATE TABLE users AS
SELECT user.username, job.jobId
FROM [job]
INNER JOIN [user] 
    ON ('|'+[user].JobID+'|' LIKE('%|'+CAST([job].JobID as varchar(20))+'|%'))

这将为你带来一张经过消毒的原子表。

Answer 3

为了为每个用户返回逗号分隔的职务列表，您可以调整Julien的代码，如下所示：

SELECT 
    U2.UserName, 
    SUBSTRING(
        REPLACE(
            REPLACE(
                        (
                        SELECT J.JobTitle
                        FROM [job] J
                        INNER JOIN [user] U
                        ON ('|'+U.JobID+'|' LIKE('%|'+CAST(J.JobID as varchar(20))+'|%'))
                        WHERE U.UserName = U2.UserName
                        FOR XML PATH('')
                        ), '</JobTitle>', ''
                    ), '<JobTitle>', ', '
                ),2,1000
            ) [JobTitles]
FROM [user] U2