SQL中的String.Join [英] String.Join in SQL

问题描述

我想从名为 RMA(简化)的表中进行选择:

i want to select from a Table called RMA(simplified):

 idRMA| RMA_Number
 -----------------------
 1      RMA0006701
 2      RMA0006730
 3      RMA0006736
 4      RMA0006739
 5      RMA0006742

RMA 和 tdefSymptomCode 之间有一个名为 trelRMA_SymptomCode 的联结表:

There is a junction table between RMA and tdefSymptomCode called trelRMA_SymptomCode:

 fiSymptomCode| fiRMA
 -----------------------
 1              1
 1              2
 2              2
 5              3
 7              3
 8              3
 2              5
 3              5
 4              5
 5              5

为了完整起见，这是tdefSymptomCode:

idSymptomCode    SymptomCodeNumber      SymptomCodeName
1                0000                   Audio problem
2                0100                   SIM problem
3                0200                   Appearance problem
4                0300                   Network problem
5                0500                   On/Off problem

问:

每个 RMA 可以有 0-5 个症状代码.如何在标量值函数中将 SymptomCodeNumber 与分隔符(如:")连接在一起，以便我只得到一个 varchar 值作为结果.

这样的东西(其中 getRmaSymptomCodes 是一个 SVF):

Something like this(where getRmaSymptomCodes is a SVF):

SELECT idRMA, RMA_Number, dbo.getRmaSymptomCodes(idRMA,':') AS Symptoms FROM RMA

这可能是 3 种不同 RMA 的症状(都只有一种症状):

This could be the symptoms of 3 different RMA's (all have exactly one symptom):

RMA_Number    SymptomCodeNumber
RMA0004823    0100
RMA0004823    0200
RMA0000083    0300
RMA0000084    0300
RMA0000084    0400

这应该连接为:

RMA0004823    0100:0200
RMA0000083    0300
RMA0000084    0300:0400

提前致谢

更新:感谢我创建了这个工作函数

Update: Thanks to all i have created this working function

CREATE FUNCTION [dbo].[getRmaSymptomCodes]
(
    @idRMA int,
    @delimiter varchar(5)
)
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @Codes VARCHAR(8000) 
    SELECT @Codes = COALESCE(@Codes + @delimiter, '') +  tdefSymptomCode.SymptomCodeNumber
    FROM  RMA INNER JOIN
        trelRMA_SymptomCode ON RMA.IdRMA = trelRMA_SymptomCode.fiRMA INNER JOIN
        tdefSymptomCode ON trelRMA_SymptomCode.fiSymptomCode = tdefSymptomCode.idSymptomCode
    where idRMA=@idRMA
    order by SymptomCodeNumber
    return @Codes
END

推荐答案

这样做可以吗?

DECLARE @Codes VARCHAR(8000) 
SELECT @Codes = COALESCE(@Codes + ', ': '') +  tdefSymptomCode.SymptomCodeNumber
    FROM  RMA INNER JOIN
        trelRMA_SymptomCode ON RMA.IdRMA = trelRMA_SymptomCode.fiRMA INNER JOIN
        tdefSymptomCode ON trelRMA_SymptomCode.fiSymptomCode = tdefSymptomCode.idSymptomCode
    where idRMA=2
    order by SymptomCodeNumber
return @Codes

这篇关于SQL中的String.Join的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！