Introduction
Lists are one of the basic structures in Mathematics, the term referring to an (ordered) set of elements separated by comma, space or any other delimiter (e.g. “:”, “;”). The elements of a list can be numbers, words, functions, or any other type of objects. In the world of databases, a list is typically formed out of the values of a given column or a given record, however it could span also a combination of rows and records, is such cases two delimiters being needed – one for column and one for row. From here comes probably the denomination of list of values. In a more general accept a list of values could be regarded as a delimited/concatenated subset. Such lists are formed when needed to send the data between the layers of an application or applications, this type of encoding being quite natural. In fact, also the data in a database are stored in similar tabular delimited structure, more complex though.An useful example in which the list of values are quite handy is the passing of multiple values within the parameter of stored procedure or function (see example). This supposes first building the list and then use the values in a dynamic build query (like in the before mentioned example) or by building a table on the fly. We can call the two operations composition, respectively decomposition of list of values.
Composition
Composition, whether on vertical or horizontal is nothing but a concatenation in which the values alternate with one or more delimiters. Let’s reconsider the concatenation based on the values of a Person.AddressType AdventureWorks table. As the logic for concatenating for one or more attributes is the same, the below example concatenates a list based on a single attribute, namely AddressTypeID in SingleList, respectively two attributes, AddressTypeID and Name.-- concatenation of values across a table ;WITH CTE (AddressTypeID, Name, Ranking) AS (--preparing the data SELECT AddressTypeID , Name , ROW_NUMBER () OVER(ORDER BY Name) Ranking FROM Person.AddressType -- WHERE ... ) , DAT (SingleList, DoubleList, Ranking) AS ( -- concatenating the values SELECT Cast(AddressTypeID as varchar(max)) SingleList , Cast('('+ Cast(AddressTypeID as varchar(10)) + ',''' + Name + ''')' as varchar(max)) DoubleList , Ranking FROM CTE WHERE Ranking = 1 UNION ALL SELECT DAT.SingleList + ',' + Cast(CTE.AddressTypeID as varchar(20)) SingleList , Cast(DAT.DoubleList + ', ('+ Cast(CTE.AddressTypeID as varchar(10)) + ',''' + CTE.Name + ''')' as varchar(max)) DoubleList , CTE.Ranking FROM CTE JOIN DAT ON CTE.Ranking = DAT.Ranking + 1 ) -- the lists SELECT SingleList , DoubleList FROM DAT WHERE Ranking = (SELECT MAX(Ranking) FROM DAT)
The second example is based on atypical delimiters, resembling to the structure built for a batch insert or table value constructor-based statement, and as we’ll see later, ideal to be used in a dynamically-built query
Decomposition
Decomposition follows the inverse path, though it’s much easier to exemplify. In fact it’s used the same technique introduced in the last example from the previous post belonging to the same cycle, Subparts of a String, in which a space was used as delimiter. Another example is the dbo.SplitList function which decomposes a string using a loop.
-- decomposition of a string to a table using CTE CREATE FUNCTION dbo.StringToTable( @str varchar(500) ,@Delimiter char(1)) RETURNS @Temp TABLE ( Id int NOT NULL ,Value varchar(50)) AS BEGIN ;WITH CTE (PrevString, Position, Word) AS ( SELECT LTrim(RTrim( CASE WHEN CharIndex(@Delimiter, @str)>;0 THEN Right(@str, Len(@str)-CharIndex(@Delimiter, @str)) ELSE '' END)) PrevString , 1 Position , LTrim(RTrim(CASE WHEN CharIndex(@Delimiter, @str)>0 THEN LEFT(@str, CharIndex(@Delimiter, @str)-1) ELSE @str END)) Word UNION ALL SELECT LTrim(RTrim(CASE WHEN CharIndex(@Delimiter, PrevString)>0 THEN Right(PrevString, Len(PrevString)-CharIndex(@Delimiter, PrevString)) ELSE '' END)) PrevString , Position + 1 Position , LTrim(RTrim(CASE WHEN CharIndex(@Delimiter, PrevString)>0 THEN LEFT(PrevString, CharIndex(@Delimiter, PrevString)-1) ELSE PrevString END)) Word FROM CTE WHERE Len(PrevString)>0 ) INSERT @Temp(Id, Value) SELECT Position , Word FROM CTE OPTION (maxrecursion 100) RETURN END
Here are two examples based on the single list created above and another one based on alphabet:
-- decomposing a list SELECT Id , value FROM dbo.StringToTable('6,1,2,3,4,5', ',') -- decomposing the "alphabet" SELECT Id , value FROM dbo.StringToTable('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z', ',')
Even if the function deals only with a delimiter, it could be used to decompose lists involving multiple delimiters, as long the list is adequately built:
-- decomposing double list SELECT Id , value , Left(value, CHARINDEX(',', value)-1) LeftValue , Right(value, len(value)-CHARINDEX(',', value)) RightValue FROM dbo.StringToTable('6,Archive;1,Billing;2,Home;3,Main Office;4,Primary;5,Shipping', ';')
The tables built thus from list of values can be further used in queries when needed to create a table on the fly. It would be interesting maybe to show that the composition and decomposition are inverse functions, however that’s out of scope, at least for current set of posts.
No comments:
Post a Comment