Concat variable #s of strings

Question

I have a table that looks like this:

Names                         uniqueid
crosby,stills,nash            1
crosby,stills,nash,young      2
crosby                        3
stills                        4
nash                          5
young                         6

I split it to:

Names                         uniqueid    name
crosby,stills,nash            1           crosby
crosby,stills,nash            1           stills
crosby,stills,nash            1           nash
crosby,stills,nash,young      2           crosby
crosby,stills,nash,young      2           stills
crosby,stills,nash,young      2           nash
crosby,stills,nash,young      2           young
crosby                        3           crosby
stills                        4           stills
nash                          5           nash
young                         6           young

What I want:

Namecombos                    count
crosby                        3
stills                        3
nash                          3
young                         2
crosby,stills                 2
stills,nash                   2
nash,young                    1
crosby,stills,nash            2
stills,nash,young             1
crosby,stills,nash,young      1

What I'm trying to do is find the unique combination of names. Crosby,Stills,Nash,Young has 10 unique combinations: Crosby Stills Nash Young Crosby,Stills Stills,Nash Nash,Young Crosby,Stills,Nash Stills,Nash,Young, Crosby,Stills,Nash,Young

Order is important.

Just need to parse out into unique combinations, then count how many result.

Hopefully that made sense.

Will there always be a row for each unique single instance of a name as is the case in your sample data or where there be a chance where, for example, Nash is only in a tuple and never alone? — S3S, May 31 '17 at 01:19
The second is correct. I think I'm on to a custom function and I will post solution if I can make it work. — T.Hannah, May 31 '17 at 01:30
It's easy to get all possible combinations but not the unique ordered combinations. i.e. crosby,stills,nash | crosby,nash,stills | stills,nash,crosby etc for all combinations. Equating these to 1 is the hard part. Some trickery with a recursive cte and stuff i'd imagine — S3S, May 31 '17 at 03:38

score 0 · Answer 1 · answered May 31 '17 at 03:49

Wow that wrecked (and racked) my brain. I'm no pro, so if someone wants to pretty up what I did, please do and I'll accept your answer!

But it does the hard part, which is creating the unique combinations. Counting them should be the easy part.

Note: I started with the original comma separated values, not the intermediate table.

CREATE FUNCTION [dbo].[func_Split] 
    (   
    @OrigStr    varchar(8000),
    @Delimiter              varchar(100) 
    )
RETURNS @tblArray TABLE
    (
    ElementID   int IDENTITY(1,1),  -- Array index
    Element     varchar(1000)               -- Array element contents
    )
AS
BEGIN

    -- Local Variable Declarations
    -- ---------------------------
    DECLARE         @Index1      smallint,
                    @Index2      smallint,
                    @incr           smallint,
                    @OuterLoop  smallint,
                    @n          smallint,
                    @LastDelim  smallint

    SET @n = LEN(@OrigStr) - LEN(REPLACE(@OrigStr, ',', ''))
    SET @OuterLoop = 1
    SET @LastDelim = LEN(@OrigStr) + 1 - CHARINDEX(@Delimiter,REVERSE(@OrigStr))

    WHILE @OuterLoop <= @n+1
    BEGIN
        SET @Index1 = 1
        SET @Index2 = @Index1

        WHILE @Index2 < LEN(@OrigStr)
        BEGIN
            SET @Index2 = @Index1

            SET @incr = 1
            WHILE @incr <= @OuterLoop
            BEGIN
                SET @Index2 = CHARINDEX(@Delimiter,@OrigStr,@Index2+1)
                IF @Index2 = 0 BEGIN SET @Index2 = LEN(@OrigStr) END
                SET @incr = @incr + 1
            END

                    IF @Index2 = LEN(@OrigStr) 
                    BEGIN 
                        INSERT INTO
                            @tblArray 
                            (Element)
                        VALUES
                            (LTRIM(RTRIM(SUBSTRING(@OrigStr,@Index1,@Index2-@Index1+1))))
                    END
                    ELSE
                    BEGIN
                        INSERT INTO
                            @tblArray 
                            (Element)
                        VALUES
                            (LTRIM(RTRIM(SUBSTRING(@OrigStr,@Index1,@Index2-@Index1))))
                    END


            SET @Index1 = CHARINDEX(@Delimiter,@OrigStr,@Index1+1)+1

        END

        SET @OuterLoop = @OuterLoop + 1



    END

    RETURN
END

Concat variable #s of strings

1 Answers1