dandenson (1/13/2011)
Thanks Wayne, your code works wonderfully. I do have a few questions though.First, I'm trying to learn/understand this. I get that ItemNumber comes from the DelimitedSplit8K function (which I have and is working). Am I right in guessing that the 'where cte1.ItemNumber =1' is just to select the first row from the returned delimitedsplit8k which is now in a temp table called CTE aliased CTE1. Then a cross join of CTE aliased to CTE2 without the first row so the cross join is doing the work of taking the second and higher values from the CSV aka rows 2+ from the CTE table and gluing the 'Username labeled, row 1 Item field?
First, I'm glad it's doing what you want... well, almost.
Yes, the ItemNumber does come from the DelimitedSplit8k function. It is sequential by the position of the data in the delimited string. In this case, the first item in the string is the UserName, so we are looking for the ItemNumber=1.
You are correct about what the CROSS JOIN is doing.
I havent seen this formula before (im kinda new), what would this be called? (the top part, I get the Cartesian join)
A kludge to work with data not properly normalized. ;-):-D
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes