SQL中的String.Join

本文介绍了SQL中的String.Join的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想从名为 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