For various reasons, developers through the years have decided to store lists of data as comma-separated lists. Most programming languages include a split() function that allows you to break apart a list of data using a specified character. T-SQL does not.
I don’t remember where I got this split function from. I know I didn’t write all of it from scratch. But basically what it will do is take a delimited list of data, split it apart, and return a table where each data element is associated with the identity value you pass into the function. This is very useful if you just want to get the values for a single row.
CREATE function Split
RETURNS @RetVal table
Insert Into @RetVal (id,Value)
Select @identityVal,value = (ltrim(rtrim(Substring(@list,1,charindex(@SplitChar,@list)-1))))
SET @list = Substring(@List,Charindex(@SplitChar,@list)+len(@SplitChar),len(@list))
INSERT INTO @RetVal (id,Value)
SELECT @identityVal, ltrim(rtrim(@list))
So what happens if you are trying to build a view or a report and need to split apart multiple rows. You can iterate over the rows in the table you want to flatten out and call the split function for each row, passing the rows PK into the split function.
create function fnFlattenMyTable
RETURNS @RetVal table
Declare @PrimaryKeyId int
Declare @MyListColumn varchar(6000)
SET @PrimaryKeyId = (select MIN(PrimaryKeyId ) FROM MyTable)
WHILE @PrimaryKeyId is not null --main loop through MyTable
Set @MyListColumn = (select MyListColumn from MyTable where PrimaryKeyId = @PrimaryKeyId )
insert into @RetVal(PrimaryKeyId , MyListValue )
select id, value from [Split](@MyListColumn , @PrimaryKeyId , ',')
SET @PrimaryKeyId = (select MIN(PrimaryKeyId ) FROM MyTable where PrimaryKeyId > @PrimaryKeyId )
This function uses one of my favorite sql tricks. It uses a while loop to avoid having to declare a cursor . The logic is pretty simple, on each loop, select the lowest value of the PK in the table that is higher than the previous PK. That’s the roundabout way of saying, “Select the next highest PK from the table”.
Right now, this function is hard coded to flatten the table “MyTable”, but it could be re-factored into a general use function.