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 ( @list varchar(8000), @identityVal int, @SplitChar char(1) ) RETURNS @RetVal table ( id int, Value nvarchar(1000) ) AS BEGIN WHILE (Charindex(@SplitChar,@list)>0) BEGIN 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)) END INSERT INTO @RetVal (id,Value) SELECT @identityVal, ltrim(rtrim(@list)) RETURN END
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 ( PrimaryKeyId int, MyListValue varchar(100) ) AS BEGIN Declare @PrimaryKeyId int Declare @MyListColumn varchar(6000) SET @PrimaryKeyId = (select MIN(PrimaryKeyId ) FROM MyTable) WHILE @PrimaryKeyId is not null --main loop through MyTable BEGIN 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 ) END RETURN END
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.