13 September 2007

Lists and a Little Math In the previous post I've shown how we can transform a delimited list in a table with the help of table-valued functions, and I gave as example a JOIN which provided the common elements between two lists. SELECT A.* FROM dbo.SplitList('23 34 50 71', ' ') A JOIN dbo.SplitList('23,34,25,45', ',') B ON A.Value = B.Value Playing with the function, but this time letting out the JOIN, obtaining thus a cartesian product. --an example SELECT * FROM dbo.SplitList('1,2,3,4', ',') A , dbo.SplitList('1,2,3,4', ',') B --another example SELECT * FROM dbo.SplitList('1,2,3,4', ',') A , dbo.SplitList('1,2,3,4', ',') B , dbo.SplitList('1,2,3,4', ',') C , dbo.SplitList('1,2,3,4', ',') D Without any apparent use, this could be handy when generating sets of testing data, instead of creating imbricated loops. A more interesting example, at least for kids, is the following. SELECT 'The ' + A.Value + ' ' + B.Value + ' ' + C.Value + '!' FROM dbo.SplitList('cat,dog,bird,fish', ',') A , dbo.SplitList('drinks,smells,eats,jumps', ',') B , dbo.SplitList('loudly,nicely,grumpy,alone', ',') C We can do even permutations, of course this example makes sense for a small number of elements: SELECT * FROM dbo.SplitList('1,2,3,4', ',') A JOIN dbo.SplitList('1,2,3,4', ',') B ON A.Value<>B.Value JOIN dbo.SplitList('1,2,3,4', ',') C ON B.Value<>C.Value AND A.Value<>C.Value JOIN dbo.SplitList('1,2,3,4', ',') D ON C.Value<>D.Value AND B.Value<>D.Value AND A.Value<>D.Value ORDER BY A.Value, B.Value, C.Value, D.Value That's all for the moment, a more useful application in a next posting.

No comments: