Comma Delimited List Day (SQL)

Two hand to forehead . . . "Smack" issues.

Neither support or contribute to best practices but manager or boss says. "Hey we are in the making folks happy business"

Issue 1:
Someone asks for a report estimate, you say "No big deal" you get into the report and see that they want everything rolled up into a comma delimited list from individual values in the table. So you think about how, without scripting something on the code end, you can present the data via SQL.

This is how in sql 2005 - 2008 and back to no big deal

Thanks to MSDN for providing simple code:
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist

(If you are still using SQL 2000 for some reason (like say the economy collapsed and the company thought updating tech was at the bottom of the priority list)the link above tells you how to do it there as well)

SELECT
t1.TeamID,
MemberList = substring((SELECT ( ', ' + FirstName )
                        FROM TeamInfo t2
                        WHERE t1.TeamID = t2.TeamID
                        ORDER BY
                           TeamID,
                           FirstName
                        FOR XML PATH( '' )
                       ), 3, 1000 )FROM TeamInfo t1
GROUP BY TeamID

Issue 2:

This is a big one, so some developer for a big Membership database at one point in history was asked to create a form and store the data, they take a select list, or checkbox list variable and decide that they just throw that thing into the database as is creating a field that stores comma delimited data.

A few years later, 5 or so, some executive who needs to look at numbers decides that they want to count the answers and report against them in a fairly complex way. So what do you do.

Thanks to Rob Volk on SQL Team and Joe Celko : here's Rob's original post

Well you need a tally table, which, in this example is simply a table with a single column "N" that has integer values 1-8000

then do something similar to the following

SELECT office_id, manager,
NullIf(SubString(',' + cast(product_list as varchar(255)) + ',' , N , CharIndex(',' , ',' + cast(product_list as varchar(255)) + ',' , N) - N) , '') AS product
FROM Tally, office_product
WHERE N <= Len(',' + cast(product_list as varchar(255)) + ',') AND SubString(',' + cast(product_list as varchar(255)) + ',' , N - 1, 1) = ','  AND CharIndex(',' , ',' + cast(product_list as varchar(255)) + ',' , N) - N > 0

So now comma delimited do and undo, done!

Mix with the pivot results earlier and really there isn't much that you can't do.