MSSQL - Concat the result of a query using IN

Question

In a MSSQL table ("username"), I have the two following records:

name   ¦  nickname
John      Johny
Marc      Marco

I'd like to do a query that would return "Johny, Marco" when I as k for the nicknames of John and Marc.

I've tried the following:

declare @consolidatedNicknames varchar(2000)
set @consolidatedNicknames = ''
select @consolidatedNicknames = @consolidatedNicknames + nickname + ';'
From username WHERE name IN ('John','Marc')

but it only returns me the nickname of 'John'.

How could it concat the nickname of 'John' AND 'Marc' ?

Many thanks.

Search for ["sql server group-concat"](http://stackoverflow.com/questions/tagged/sql-server-group-concat) — Andomar, Jun 05 '13 at 14:21
Seems to work as expected for me: http://sqlfiddle.com/#!3/6bfbe/1 — Lamak, Jun 05 '13 at 14:28
@Lamak - May be you should point out to OP that you didn't use his/her script "as is". — PM 77-1, Jun 05 '13 at 14:39
@PM77-1 What do you mean?, the only difference is that I added a `SELECT` to see what value was in the variable. — Lamak, Jun 05 '13 at 14:42
@Lamak - Sorry, may be I misunderstood OP requirements. I thought that he wanted the concatenated string to be output. Anyway, that additional select was all that I meant. — PM 77-1, Jun 05 '13 at 14:47

score 0 · Accepted Answer · answered Jun 05 '13 at 14:38

0

Your sample code worked as expected for me. I've had a few problems with that method with large strings (50k characters or more). Here is a different version that I've found a bit more robust.

DECLARE @consolidatedNicknames varchar(2000)
SET @consolidatedNicknames = ''

SELECT @consolidatedNicknames = 
        STUFF((SELECT ', ' + username.nickname
                FROM username
                WHERE name IN ('John','Marc')
                FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
            , 1, 2, '')

answered Jun 05 '13 at 14:38

Kenneth Fisher

3,692
19
21

Thanks for your quick reply. However when I "print @consolidatedNicknames" I only get "Marco", and not "Johny,Marco" as I am expecting... – user2456059 Jun 05 '13 at 14:54
My guess is you have something wrong with your data. You'll notice on several comments that your existing code works when we try it. Either that or you have some setting interfering. Try running `SELECT username.nickname FROM username WHERE name IN ('John','Marc')` and see what you get. – Kenneth Fisher Jun 05 '13 at 15:09
Thanks, I do not know what was going on, but you're right, it seems to be working! An thanks for the more robust function. – user2456059 Jun 05 '13 at 15:11

Declan_K · Answer 2 · 2013-06-05T14:46:33.723

STUFF and XML are what you need. Have a look at this article

Here is a function I have build to do something very similar.

CREATE FUNCTION [dbo].[CapOwnerList]
    (
    @Seperator nvarchar(100) = ','
    )

    RETURNS @ConcatValues TABLE
        (
        ID DECIMAL(28,0) NOT NULL
        ,VALUE NVARCHAR(MAX)
        )
    AS
    BEGIN
        DECLARE @TempTable TABLE (ID INT, VAL VARCHAR(MAX));

        INSERT INTO @TempTable (ID,VAL)
        SELECT  C2O.CAP_ID
                ,FP.NAME
        FROM    REL_CAP_HAS_BUS_OWNER C2O
        INNER JOIN
                FACT_PERSON FP
        ON      C2O.PERSON_ID = FP.ID
        ORDER BY
                FP.NAME
        ;

        IF RIGHT(@Seperator,1)<>' '
            SET @Seperator = @Seperator+' ';

        INSERT @ConcatValues
        SELECT  DISTINCT 
                T1.ID
                ,STUFF((SELECT @Seperator + T2.VAL FROM @TempTable AS T2 WHERE T2.ID = T1.ID FOR XML PATH('')), 1, LEN(@Seperator), '') AS VALS
        FROM    @TempTable AS T1;

        RETURN;
    END

GO

Be aware that using the `FOR XML` method without using `TYPE).value('.', 'NVARCHAR(MAX)`, as in the above answer will cause special characters (e.g. `>`) to be escaped. — GarethD, Jun 05 '13 at 15:20

MSSQL - Concat the result of a query using IN

2 Answers2