在SQL中拆分和连接值 [英] Split and concatenate values in SQL

问题描述

我有两个不同的表，如下所示，具有sql查询的特定要求。

I have two different tables as below and have specific requirement of a sql query.

Table1:
Name   RuleNumber
Tom    1,2
Pete   1,3

Table2:
RuleNumber  Description
1           Rule1
2           Rule2
3           Rule3

如何获得类似下面的SQL查询结果

How can I get a sql query result something like below

Name    Description
Tom     Rule1, Rule2
Pete    Rule1, Rule3

推荐答案

感谢您的澄清。你可以解决这个问题，但这对人类来说太恶心了。更改数据库架构并迁移您拥有的数据。如果这超出了您的范围，请退出项目，公司或整个领域。无论如何，使用这种愚蠢行为并不能带来回报。我希望你知道什么是关系模型。

-SA

Thank you for clarification. You can work around the problem, but it''s too disgusting for a human. Change the database schema and migrate the data you have it. If this is beyond your reach, quit the project, company or the whole field. Working with such foolishness does not pay off anyway. I hope you know what is the relational model.

—SA

如果有人在寻找类似的东西在这里是解决方案。



我需要一个自定义拆分函数来分隔分隔列表，然后使用FOR XML PATH来组合描述。这是最终查询

If anybody looking for similar things here is the solution.

I needed a custom split function to separate the delimited list, and then use FOR XML PATH to combine the descriptions. Here is the final query

select  t1.Name,
        STUFF(( SELECT ', ' + Description
                FROM    table2 AS t2 
                WHERE   t2.ruleNumber in (select s from dbo.SplitFunction(t1.RuleNumber, ','))
        ORDER BY ruleNumber
        FOR XML PATH('')), 1, 1, '') as 'Description'
from    table1 t1

以下是拆分功能的代码。

Here is the code for the split function.

create function [dbo].[SplitFunction]
(
    @String     varchar(8000) ,
    @Delimiter  varchar(10)
)
returns @tbl table (s varchar(1000))
as

begin
declare @i int ,
    @j int
    select  @i = 1
    while @i <= len(@String)
    begin
        select  @j = charindex(@Delimiter, @String, @i)
        if @j = 0
        begin
            select  @j = len(@String) + 1
        end
        insert  @tbl select substring(@String, @i, @j - @i)
        select  @i = @j + len(@Delimiter)
    end
    return
end

这篇关于在SQL中拆分和连接值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！