Sql Query to retrieve following data

Question

I have got a table with 3 columns. The first two columns are ID (Primary Key) and Country. The third column contains some names seprated by colon character. For example:

ID    Country    Names
--------------------------
 1     USA       Mike;Bill
 2     USA       Michael;Lara;Van
 3     Italy     Kobe;Nate;Tim;Manu

I need to write an SQL query that will generate a new row for each name. For example, in this case the output will be

ID    Country    Name
--------------------------
 1    USA        Mike
 1    USA        Bill
 2    USA        Michael
 2    USA        Lara
 2    USA        Van
 3    Italy      Kobe
 3    Italy      Nate
 3    Italy      Tim
 3    Italy      Manu

How can I do that? I have found a split function in t-sql which can split the string about a character. But how to split the data into multiple rows?

What is the maximum number of separated values in the third column? — Brian DeMilia, Jan 06 '15 at 22:59

score 2 · Accepted Answer · edited May 23 '17 at 10:26

The task is to split the names into their own rows, then get those rows into a single result set.

We can use this function from this QA as a split function ( T-SQL: Opposite to string concatenation - how to split string into multiple records ) as it's a table-valued function we can work with the resultset as though it were any other form of tabular data (e.g. a table, a view, a temporary table, a table variable, etc)

CREATE FUNCTION dbo.SplitStr( @sep char(1), @s nvarchar(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
)

Then we need to call this function for each row in the CountryNames table and combine the results. This is actually a single-liner if we use CROSS APPLY, so the query needed is just this:

SELECT
    CountryNames.ID,
    CountryNames.Country,
    Splat.s
FROM
    CountryNames
    CROSS APPLY
    dbo.SplitStr(';', CountryNames.Names ) As Splat

Ta-da!

Note that if this a high-performance application or working with a large table it might make sense to perform this data transformation in application code. At the very least try to normalize your data so you won't need to run this code every time you want to look this information up.

Good call, table valued split/parse function is much better for this problem. — Hart CO, Jan 06 '15 at 23:24

score 0 · Answer 2 · answered Jan 06 '15 at 23:15

You could create a new table and use a loop to INSERT:

SET NOCOUNT ON
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <= (SELECT MAX(LEN(REPLACE(Names,';',';;')) - LEN(Names)) FROM YourOldTable))
BEGIN

INSERT INTO YourNewTable
SELECT  ID,Country
      , Name = dbo.FN_PARSE([Names],';',@intFlag)
FROM  YourOldTable
WHERE  dbo.FN_PARSE([Names],';',@intFlag) IS NOT NULL

SET @intFlag = @intFlag + 1
END
GO
SET NOCOUNT OFF

Your syntax may vary based on your split/parse function.

Sql Query to retrieve following data

2 Answers2