String.Join in SQL

Question

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

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

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

for the sake of completeness, this is 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

Q:

every RMA can have 0-5 SymptomCodes. How can i join the SymptomCodeNumber with a delimiter like ':' together in a scalar-valued-function, so that i only get one varchar-value as result.

Something like this(where getRmaSymptomCodes is a SVF):

SELECT idRMA, RMA_Number, dbo.getRmaSymptomCodes(idRMA,':') AS Symptoms FROM 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

That should be concatenated as:

RMA0004823    0100:0200
RMA0000083    0300
RMA0000084    0300:0400

Thank you in advance

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

http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Mohsen, Nov 21 '12 at 07:32
@Mohsen: for SQL-Server this link is better: http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-sql-server-function-to-join-multiple-rows-from-a-su — Tim Schmelter, Nov 21 '12 at 08:51

score 4 · Accepted Answer · answered Jan 27 '11 at 11:00

4

Would this do?

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

answered Jan 27 '11 at 11:00

grimmig

1,391
2
14
24

With a little adjustment(see my update), it works. Thank you very much. – Tim Schmelter Jan 27 '11 at 11:30

score 3 · Answer 2 · answered Jan 27 '11 at 11:42

Use the classic XML PATH-trick for this:

declare @RMA  as table(idRMA int, RMA_Number nvarchar(20))

declare @trelRMA_SymptomCode as table (fiSymptomCode int, fiRMA int)

declare @tdefSymptomCode as table (idSymptomCode int, SymptomCodeNumber nvarchar(4), SymptomCodeName nvarchar(20))

insert into @RMA values
(1,      'RMA0006701'),
(2,      'RMA0006730'),
(3,      'RMA0006736'),
(4,      'RMA0006739'),
(5,      'RMA0006742')

insert into @trelRMA_SymptomCode values
(1,              1),
(1,              2),
(2,              2),
(5,              3),
(7,              3),
(8,              3),
(2,              5),
(3,              5),
(4,              5),
(5,              5)

insert into @tdefSymptomCode values
(1,                '0000',                   'Audio problem'),
(2,                '0100',                   'SIM problem'),
(3,                '0200',                   'Appearance problem'),
(4,                '0300',                   'Network problem'),
(5,                '0500',                   'On/Off problem')

select RMA_Number,
 STUFF(
    (
    SELECT
      ':' + SymptomCodeNumber
    FROM @tdefSymptomCode def 
    join @trelRMA_SymptomCode rel on def.idSymptomCode = rel.fiSymptomCode
    where rel.fiRMA=rma.idRMA
    FOR XML PATH('')
    ), 1, 1, '')
from @rma rma

results in

RMA0006701  0000
RMA0006730  0000:0100
RMA0006736  0500
RMA0006739  NULL
RMA0006742  0100:0200:0300:0500

score 2 · Answer 3 · answered Jan 27 '11 at 11:52

select rma_number,
   stuff((select ':' + c.symptomcodenumber
    from trelRMA_SymptomCode r
    inner join tdefsymptomcode c on c.idsymptomcode = r.fisymptomcode
    where r.fiRMA = rma.idRMA
    order by c.symptomcodenumber
    for xml path('')), 1, 1, '') SymptomCodeName
from rma

score 1 · Answer 4 · answered Jan 27 '11 at 10:41

1

Here is something that should get you going:

ALTER FUNCTION [dbo].[GetUCColumns]
(@tableid INT, @index INT)
RETURNS VARCHAR (MAX)
AS
BEGIN

    DECLARE @cols varchar(max)

    SELECT @cols = COALESCE(@cols + ', ', '') + [name]
    FROM [sys].[columns]
    where object_id = @tableid AND column_id IN 
    (SELECT [column_id]
      FROM [sys].[index_columns]
      where object_id = @tableid AND @index = index_id)

    RETURN @cols
    END

answered Jan 27 '11 at 10:41

leppie

115,091
17
196
297

I'm not sure, but does this sql not return the column names of given tables? My requirement was completely different: i need the resultset of every symptomcode that belongs to a RMA(could be 0-5) as a varchar delimited by a char. For example: `0100,0200,0300` if 3 symptoms belong to this rma-record and the delimiter is a comma.Have a look at my updated question. Thank you anyway. – Tim Schmelter Jan 27 '11 at 11:01
@Tim: Same approach. See answer above. – leppie Jan 27 '11 at 11:21

String.Join in SQL

Q:

4 Answers4

Linked