SQL comma-delimited field statistics (from the network)

Because many business tables use a design pattern that violates the first paradigm because of historical reasons or performance reasons. That is, multiple attribute values are stored in the same column (see the following table for specific structures). In this mode, the application often needs to divide the column by delimiter and get the result of the column change. Table data: IDValue1Tiny,small,big2Small,medium3Tiny,bigExpect to get results: IDValue1Tiny1Small1Big2Small2Medium3Tiny3BigBody:  www.2cto.com   #需要处理的表create table tbl_name (ID int, msize varchar); INSERT into tbl_name values (1, ' Tin Y,small,big '); insert into tbl_name values (2, ' small,medium '); insert into tbl_name values (3, ' tiny,big ');  # Self-add table for looping create TABLE incre_table (Autoincreid int), insert into incre_table values (1), insert into incre_table values (2); Nsert into incre_table values (3),  select A.id,substring_index (Substring_index (a.msize, ', ', B.autoincreid), ', ', -1)  from tbl_name ajoinincre_table bon b.autoincreid <= (Length (a.msize)-Length (replace (a.msize, ', ', ') )) +1) Order by a.id;  Principle Analysis: The most basic principle of this join is the Cartesian product. This is the way to implement loops. The following is a detailed analysis of the problem: Length (a.size)-Length (replace (a.msize, ', ', ')) +1   indicates that after the comma is divided, the number of values to be changed to the column, hereinafter referred to as the Njoin process pseudo-code: loop by ID {Judging: I whether <= n{obtains the data closest to the first comma, namely the Substring_index (Substring_index (a.msize, ', ', b.id), ', ', -1) i = i +1 }id = ID +1&n BSP;}  www.2cto.com    Summary: The downside of this approach is that we need a stand-alone table with sequential numbers (this is incre_table). and the maximum value of a continuous series must be greater than the number of values that match the split. For example, a row of msize With 100 comma-separated values, our incre_table need to have at least 100 contiguous rows. Of course, MySQL also has a ready-made list of contiguous numbers available. If mysql.help_topic:help_topic_id a total of 504 values, generally can meet the majority of demand. Rewritten as follows:  select A.id,substring_index (Substring_index (a.msize, ', ', b.help_topic_id+1), ', ', -1)  from  Tbl_name ajoinmysql.help_topic bon b.help_topic_id < (length (a.msize)-Length (replace (a.msize, ', ', ')) +1) Order by a . id;   ========================================================================= Problem: 

There is a field in the table author, as follows
ID        Author
1         Zhang San
2         Zhang San, John Doe
3         Harry
4         John Doe
5         Zhang San, John Doe, Harry

Now I want to find out the results.
Author        Count
Zhang San            3
John Doe            3
Harry            2


SQL Server answers:

     

Select  
     Author=substring (A.author,b.id,charindex (', ', a.author+ ', ', b.id)-b.id), COUNT (*)
From  
     

     CHARINDEX (', ', ', ' +a.author,b.id ') =b.id
GROUP BY SUBSTRING (A.author,b.id,charindex (', ', a.author+ ', ', b.id)-b.id);

Table1 Replace with your own table name, Top 100 that 100 you replace a slightly larger number.

SQL comma-delimited field statistics (from the network)