Create Multiple rows from one row

Question

I have a table called Institution.

InstID       InstName                                  RegionsCovered

100           Bank of America                        CA,NJ,OR,WA

200           Wells Fargo                               CA,NV

I would like the output to look like the following

100, Bank of America, CA

100, Bank of America, NJ

100, Bank of America, OR

100, Bank of America, WA

200, Wells Fargo, Ca

200, Wells Fargo, NV

How can I do this?

Thanks

 

 

Answer 1

If you need to handle delimited lists, you want to create a Function that splits them up.  So first create the function

CREATE FUNCTION dbo.Split (@DelimitedString nvarchar(max), @Delimiter nvarchar(10))
RETURNS table
AS
RETURN (
    WITH Pieces (ID, start, stop) AS (
      SELECT CAST(1 AS bigint), CAST(1 AS bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString) AS bigint)
      UNION ALL
      SELECT ID + 1, CAST(stop + 1 As bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString, stop + 1) AS bigint)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT ID,
      SUBSTRING(@DelimitedString, start, CASE WHEN stop > 0 THEN stop-start ELSE LEN(@DelimitedString) END) AS Element
    FROM Pieces
  )
GO

Then all you need to do is

Select i.InstID, i.InstName, s.Element As Region
From Instition i
Cross Apply dbo.Split(i.RegionsCovered, ',') s
Order By i.InstID, Region;

Tom

Answer 2

Another option - Adopted from code posted here: http://stackoverflow.com/questions/4250475/split-one-column-into-multiple-rows

DECLARE @tbl TABLE ( InstID int, InstName nvarchar(50), RegionsCovered nvarchar(50))

INSERT INTO @tbl (InstID,InstName,RegionsCovered) VALUES
(100,'Bank of America','CA,NJ,OR,WA'),
(200,'Wells Fargo','CA,NV');

;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3)
SELECT  InstID, InstName,        
        LTRIM(RTRIM(SUBSTRING(tbl.RegionsCovered, nums.n, charindex(N',', tbl.RegionsCovered + N',', nums.n) - nums.n))) AS [Region]
FROM   Numbers AS nums INNER JOIN @tbl AS tbl ON nums.n <= CONVERT(int, LEN(tbl.RegionsCovered)) AND SUBSTRING(N',' + tbl.RegionsCovered, n, 1) = N','
ORDER BY InstID, Region

Answer 3

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/b72e0084-c8e3-488f-bd6b-edf09ef54ae0