In SQL, can i convert a "|" separated list of IDs to a list of their values?

Question

Using states as an example, my current data looks like

StateAbbrev  | NumOfResults
-----------    ------------
MD           | 5
VA           | 2
DC           | 7
MD|VA        | 2
CA|NY|VA     | 1

I would like it to output the following

StateName                    | NumOfResults
---------                      ------------
Maryland                     | 5
Virginia                     | 2
District of Columbia         | 7
Maryland,Virginia            | 2
California,New York,Virginia | 1

I do have a table that can map StateAbbrev to StateName

I could create a function that would just take the list if pipe separated IDs and spit out a comma separated lists, but i have more then 1 table with this scenario, so i am looking for a cleaner solution. Thank you!

Answer 1

I use the following UDF to handle splits (via 4guysfromrolla)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split]
(
    @List nvarchar(2000),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(    
    Id int identity(1,1),
    Value nvarchar(100)
) 
AS  
BEGIN
    While (Charindex(@SplitOn,@List)>0)
    Begin 
     Insert Into @RtnValue (value)
     Select
      Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
     Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
    End 

    Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))

    Return
END

The following is an example of how to use the UDF:

select [Value] from [dbo].Split('CA|NY|VA', '|')

Which would return you a table with 3 rows in it, one for each of the states.

UPDATE:

Here is the full call to do the translation first:

declare @cslist varchar(2056)
select @cslist = coalesce(@cslist+',', '') + translated_states.full_state
FROM (

    select T2.full_state from [dbo].Split('CA|NY|VA', '|') as T1
    JOIN test as T2
    on T2.abbr_state = T1.[Value]

) translated_states
select @cslist

Let me know if you have any questions about what needs to be updated to make this work with your tables rather than my test tables.

Answer 2

You can write a C# function and then use SQLCLR to do it: http://msdn.microsoft.com/en-us/library/ms345136%28SQL.90%29.aspx

Or you could just write a T-SQL function to do it.

Answer 3

I came up with the following, but I had to use a temp table to do it; for some reason, the subquery wouldn't work with SUBSTRING.

DECLARE @T TABLE ( col VARCHAR(1000) )

INSERT  INTO @T
        ( col 
        )
        SELECT  'MD'
        UNION ALL
        SELECT  'MD|CA'
        UNION ALL
        SELECT  'MD|CA|VA'

DECLARE @states TABLE
    (
      abbr VARCHAR(2) ,
      StateName VARCHAR(10)
    )

INSERT  INTO @states
        SELECT  'MD' ,
                'Maryland'
        UNION ALL
        SELECT  'CA' ,
                'California'
        UNION ALL
        SELECT  'VA' ,
                'Virginia'



SELECT  col ,
        word = SUBSTRING('|' + col + '|', Number + 1,
                         CHARINDEX('|', '|' + col + '|', Number + 1) - Number
                         - 1) ,
        Number
INTO    #tmp
FROM    ( SELECT /*use a table of numbers if you have one instead of this subquery*/
                    ROW_NUMBER() OVER ( ORDER BY ( ac1.Object_ID ) ) AS Number
          FROM      Master.sys.columns ac1
        ) Numbers
        CROSS APPLY @t t
WHERE   Number >= 1
        AND Number < LEN('|' + col + '|') - 1
        AND SUBSTRING('|' + col + '|', Number, 1) = '|'
ORDER BY col ,
        Number

SELECT  t1.col ,
        StateName = REPLACE(( SELECT    StateName AS [data()]
                              FROM      #tmp t
                                        JOIN @states s ON t.word = s.abbr
                              WHERE     t1.col = t.col
                              ORDER BY  COL ,
                                        Number
                            FOR
                              XML PATH('')
                            ), ' ', '|')
FROM    #tmp t1



DROP TABLE #tmp

Answer 4

Here's a non-UDF solution which uses a Numbers table, XML and CROSS APPLY (twice):

DECLARE @Numbers TABLE (Num INT);

INSERT INTO @Numbers VALUES (1);
INSERT INTO @Numbers VALUES (2);
INSERT INTO @Numbers VALUES (3);
INSERT INTO @Numbers VALUES (4);
INSERT INTO @Numbers VALUES (5);
INSERT INTO @Numbers VALUES (6);
INSERT INTO @Numbers VALUES (7);
INSERT INTO @Numbers VALUES (8);
INSERT INTO @Numbers VALUES (9);

DECLARE @Results TABLE (StateAbbrevs VARCHAR(255), NumOfResults INT);

INSERT INTO @Results VALUES ('MD', 5);
INSERT INTO @Results VALUES ('VA', 2);
INSERT INTO @Results VALUES ('DC', 7);
INSERT INTO @Results VALUES ('MD|VA', 2);
INSERT INTO @Results VALUES ('CA|NY|VA', 1);

DECLARE @Abbrev TABLE (StateAbbrev VARCHAR(2), StateName VARCHAR(255));

INSERT INTO @Abbrev VALUES ('MD', 'Maryland');
INSERT INTO @Abbrev VALUES ('VA', 'Virginia');
INSERT INTO @Abbrev VALUES ('DC', 'District of Columbia');
INSERT INTO @Abbrev VALUES ('CA', 'California');
INSERT INTO @Abbrev VALUES ('NY', 'New York');

--SELECT * FROM @Results;
--
--SELECT * FROM @Abbrev;

SELECT  STUFF(StateNames, 1, 1, '') AS StateNames,
        NumOfResults
FROM    @Results AS RESULTS0
        CROSS APPLY ( SELECT    ',' + ABBREV.StateName
                      FROM      ( SELECT    PVT.StateAbbrev,
                                            RESULTS.StateAbbrevs
                                  FROM      @Results AS RESULTS
                                            CROSS APPLY ( SELECT    SUBSTRING(RESULTS.StateAbbrevs, NUMBERS.Num, CHARINDEX('|', RESULTS.StateAbbrevs + '|', NUMBERS.Num) - NUMBERS.Num) AS StateAbbrev
                                                          FROM      @Numbers AS NUMBERS
                                                          WHERE     NUMBERS.Num <= LEN(RESULTS.StateAbbrevs)
                                                                    AND SUBSTRING('|' + RESULTS.StateAbbrevs, NUMBERS.Num, 1) = '|'
                                                        ) AS PVT
                                ) AS X
                                LEFT JOIN @Abbrev AS ABBREV ON ABBREV.StateAbbrev = X.StateAbbrev
                      WHERE     StateAbbrevs = RESULTS0.StateAbbrevs
                    FOR
                      XML PATH('')
                    ) AS Y ( StateNames )