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
How do we do that?
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
;
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
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?