Split values then resolve the values to a name

Question

I need to be able to do something with my column (below) that can contain multiple values. The 'HearAboutEvent' column has multiple values separated by a comma. Each one of these values corresponds to an entry in another table. So the value of 11273 will equal facebook, 11274 will mean radio, and 11275 will mean commercial.

The data I am working with looks like this:

weather ID  MemberID    SubscriptionID  DateEntered     ParticipatedBefore  ParticipatedBeforeCities    WeatherDependent    NonRefundable   TShirtSize  HearAboutEvent
Yes     24      18          1           2013-12-19          0                   NULL                        10950           10952           10957       11273, 11274, 11275

I am able to do the proper join to resolve the value of 'weather', note it is the first column and the 8th column.

This is the query I have created so far to resolve the values of WeatherDependent:

SELECT CFS1.Name as 'weather', *
FROM FSM_CustomForm_693 t
LEFT JOIN FSM_CustomFormSelectOptions CFS1 ON CFS1.ID = t.WeatherDependent 
where t.ID = 24

Ultimately I need to have the data look like this:

weather ID  MemberID    SubscriptionID  DateEntered     ParticipatedBefore  ParticipatedBeforeCities    WeatherDependent    NonRefundable   TShirtSize  HearAboutEvent
Yes     24      18          1           2013-12-19          0                   NULL                        10950           10952           10957       Facebook, radio, commercial

What you need is a split string function. There are plenty of solutions out there. Here's one (http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco). Once you get the string apart and into separate rows you can join it to the other table. — SQLChao, Jul 07 '14 at 19:59

score 0 · Answer 1 · answered Jul 09 '14 at 15:46

Things I think you could use to accomplish this are:

A Split TVF FUNCTION - http://msdn.microsoft.com/en-us/library/ms186755.aspx
CROSS APPLY - http://technet.microsoft.com/en-us/library/ms175156.aspx
STUFF & FOR XML PATH - http://msdn.microsoft.com/en-us/library/ms188043.aspx & http://msdn.microsoft.com/en-us/library/ms190922.aspx

Going one step further, you need something like this: Excuse my profuse use of sub queries.

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
GO

SELECT 
O.A,O.B,O.C,O.D,O.E,O.F,O.G,O.H,O.I,O.J,O.Stuffed
FROM (
    SELECT
    * 
    ,STUFF((
        SELECT ', ' + Name
        FROM (
            SELECT 
            V.*
            ,Y.Name
            FROM (
                SELECT 
                'Yes' AS A
                ,24 AS B
                ,18 AS C
                ,1 AS D
                ,'2013-12-19' AS E
                ,0 AS F
                ,NULL AS G
                ,10950 AS H
                ,10952 AS I
                ,10957 AS J
                ,'11273, 11274, 11275' AS K
            )
            AS V
            CROSS APPLY dbo.Split(',',REPLACE(K,' ','')) AS P
            JOIN (
                SELECT 11273 AS Id , 'Facebook' AS Name UNION ALL
                SELECT 11274 AS Id , 'radio' AS Name UNION ALL
                SELECT 11275 AS Id , 'commercial' AS Name
            )Y ON y.Id = p.s) ExampleTable
            FOR XML PATH('') 
            ), 1, 1, '' )
    AS [Stuffed]
    FROM (
        SELECT 
        V.*
        FROM (
            SELECT 
            'Yes' AS A
            ,24 AS B
            ,18 AS C
            ,1 AS D
            ,'2013-12-19' AS E
            ,0 AS F
            ,NULL AS G
            ,10950 AS H
            ,10952 AS I
            ,10957 AS J
            ,'11273, 11274, 11275' AS K
        )
        AS V
        CROSS APPLY dbo.Split(',',REPLACE(K,' ','')) AS P
        JOIN (
            SELECT 11273 AS Id , 'Facebook' AS Name UNION ALL
            SELECT 11274 AS Id , 'radio' AS Name UNION ALL
            SELECT 11275 AS Id , 'commercial' AS Name
        )Y ON y.Id = p.s
    )Z 
) O
GROUP BY O.A,O.B,O.C,O.D,O.E,O.F,O.G,O.H,O.I,O.J,O.K,O.Stuffed

Split values then resolve the values to a name

1 Answers1