Click here to Skip to main content
Click here to Skip to main content

Converting comma separated data in a column to rows for selection

, 24 Feb 2014
Rate this:
Please Sign up or sign in to vote.
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:

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

License

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

Share

About the Author

OriginalGriff
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?
Follow on   Google+

Comments and Discussions

 
QuestionAlternatively... PinprofessionalRichard Deeming7-Mar-14 2:14 
GeneralSometimes you just gotta PinmemberDavid Days25-Feb-14 7:00 
GeneralRe: Sometimes you just gotta PinprotectorOriginalGriff25-Feb-14 8:09 
GeneralMy vote of 5 PinmemberDavid Days25-Feb-14 6:53 
GeneralMy vote of 3 Pinmemberjnicho24-Feb-14 4:42 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140902.1 | Last Updated 24 Feb 2014
Article Copyright 2014 by OriginalGriff
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid