Click here to Skip to main content
16,015,697 members
Articles / Database Development / SQL Server
Tip/Trick

Converting comma separated data in a column to rows for selection

Rate me:
Please Sign up or sign in to vote.
4.88/5 (5 votes)
24 Feb 2014CPOL1 min read 66.4K   10   6
If you have an SQL column which contains a comma separated list of names, you may have wondered how to return it as individual rows in a SELECT statement. It's not too bad, really!

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:

SQL
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:

SQL
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
CEO
Wales Wales
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?

Comments and Discussions

 
QuestionConverting Comma Separated Data Pin
Borealis75924-Apr-17 10:42
Borealis75924-Apr-17 10:42 
QuestionAlternatively... Pin
Richard Deeming7-Mar-14 2:14
mveRichard Deeming7-Mar-14 2:14 
GeneralSometimes you just gotta Pin
David Days25-Feb-14 7:00
professionalDavid Days25-Feb-14 7:00 
GeneralRe: Sometimes you just gotta Pin
OriginalGriff25-Feb-14 8:09
mveOriginalGriff25-Feb-14 8:09 
GeneralMy vote of 5 Pin
David Days25-Feb-14 6:53
professionalDavid Days25-Feb-14 6:53 
GeneralMy vote of 3 Pin
jdionicio24-Feb-14 4:42
professionaljdionicio24-Feb-14 4:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.