SQL Perform Relational Join To Delimited Field Data

[peridian]

Hi,

For some daft reason, the database I've been asked to look at stores a series of tickboxes from a website into a single field, with a delimiter separating them.

E.g.

RecordID | TickBoxCodes
------------ | -----------------
1 | TB1;TB4;TB6
2 | TB2;TB4
3 | <blank>
4 | TB1;TB3

Somehow I need to produce a GROUP BY query for this table that produces a summary of the data in the rest of the record grouped by the individual items in the delimited field.

E.g.

TickBoxCode | RecordCount
------------------ | -------------------
TB1 | 2
TB2 | 1
TB3 | 1
TB4 | 2
etc....

I have found various table UDFs that can take parameters in and delimit the result and output a table result. I figured great, I could pass in each row and join on the output to simulate the relational structure.

Unfortunately that only works using parameters for the input of the UDF, I cannot pass the function table/field names directly in the query.

E.g. this is what I thought I could do....

SELECT CodeTable.OutputID, CodeTable.ResultingSplitData, OT.RestOfFields
INTO #sometemptable
FROM dbo.OriginalTable OT,
dbo.SplitToTable(OT.IdentifierColumn, OT.DelimitedDataColumn, ';') CodeTable
 

Any ideas how this could be done in SQL?

Regards,
Rob.

[ck9663]

How many records max(recordid) are we talking about here?

~~ CK