Converting comma separated data in a column to rows for selection

Introduction

Suppose you have a table with two columns:

FlatNo    Residents
   1      David Eddings,Terry Pratchett,Greg Bear
   2      Gregory Benford,Orson Scott Card,David Brin,Raymond E Feist

Suppose you want to return this as a single table, organised by flat and individual resident?

Well personally, I'd create a table that had a row for the flat number and resident name for each person rather than using a comma delimited list as a column, but sometimes we have to live with others mistakes and just get on with it... What we want is:

Flat	Resident
  1	David Eddings
  1	Terry Pratchett
  1	Greg Bear
  2	Gregory Benford
  2	Orson Scott Card
  2	David Brin
  2	Raymond E Feist

Doing the work  

It's not too bad - the code that does the hard work is something I've published as a Tip before: Using comma separated value parameter strings in SQL IN clauses[^] but slightly modified to keep the ID (in this case flat number) together with the resident. All you need to do is define an SQL Function which splits the string and creates a temporary table together with the ID value:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CSVToTableWithID] (@ID INT, @InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
   (tempid int IDENTITY(1,1) not null, 
   Id int not null,
   Data NVARCHAR(MAX))
AS
BEGIN
    ;-- Ensure input ends with comma
	SET @InStr = REPLACE(@InStr + ',', ',,', ',')
	DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%,%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO @TempTab(Id, Data) VALUES (@ID,@VALUE)
END
	RETURN
END
GO 

Using the code 

Then all we have to do is feed each row value into the function, and select that into the result. Happily, we can do this very easily with a CROSS APPLY:

SELECT ca.Id AS [Flat], ca.Data AS [Resident] FROM MyTable t
CROSS APPLY (SELECT * FROM [CSVToTableWithID](t.FlatNo, t.Residents)) ca

Points of Interest

Seriously: use separate rows if you can. Comma delimited data within SQL columns are a PITA to work with! It's a heck of a lot easier to reformat separate rows into comma separated values on the odd occasion you need it than to work with them all the time.

History

Original Version