SQL Server 2012 在一个 varchar(1200) 中拆分出多个值

Question

在SQL Server 2012数据库中，我应该计算每个"罐装"消息在上一学年和当前学年中用于小学生的次数。

现在我有以下T-SQL工作：

USE TEST
SELECT 
    GS.Comments, COUNT(*) AS [Counts] 
FROM 
    dbo.Enrol Enrol
JOIN 
    dbo.Student Student ON Student.StudentID = Enrol.StudentID 
JOIN 
    dbo.GS GS ON GS.StudentID = Student.Studentid
              AND (GS.Comments IS NOT NULL)
              AND (GS.Comments <> '')
WHERE
    Enrol.grade IN ('KG', '01', '02', '03', '04', '05', '06')
    AND Enrol.endYear BETWEEN 2016 AND 2017 
GROUP BY
    GS.Comments
ORDER BY
    Counts DESC, GS.Comments ASC

问题是GS.Comments列定义为 varchar(1200) 。列中可以有一条消息和/或此列中可以有很多消息。每条消息都以句点结尾，每条消息之间都有一个空格。

一个GS.Comments列中的多封邮件的示例如下所示：

The student is trying hard and needs to make their time more efficiently. This student is good at math. This student turns in their assignments on time. This student seems to enjoy school.

当一条消息位于"一个GS.Comments"列中时的示例如下所示：

This student seems to enjoy school.

因此，当GS.Comments列包含多条消息和/或仅一条消息时，我会向我展示可以使用的 T-SQL 逻辑，以便我可以计算每个唯一消息的使用次数？

Answer 1

您可以使用以下链接按期间拆分列。 然后，在新形成的列上进行简单的分组应该可以让您计算它。

将 SQL 列中的分隔值拆分为多行

Answer 2

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
Alter proc [dbo].[StringSplitIntoRows]
(
@tbl varchar(100),---table name as parameter
@col varchar(100)---column name as parameter
)
As
Begin
--creating two temp tables
If OBJECT_ID('tempdb..#test1') is not null drop table #test1
create table #test1(tempcol varchar(200))
--inserting the table(has comma seperated string column) data into temp table 
    Declare @tempresult nvarchar(500)
    set @tempresult = 'insert into #test1(tempcol) select ' + quotename(@col) + ' from ' + quotename(@tbl)
    exec sp_executesql @tempresult
If OBJECT_ID('tempdb..#test2') is not null drop table #test2
            create table #test2(tempcol1 varchar(200))
    Declare @Name varchar(200)
    Declare @real VARCHAR(100) 
    declare split cursor for ---declared a cursor to fetch  row by row data 
            select tempcol from #test1 --temp table which has comma seperated string in column
open split 
        fetch next from split into  @Name
    while (@@FETCH_STATUS=0)
    Begin
            declare @temp int=0
            declare @result nvarchar(MAX)=''
            declare @begin int=0    
        while CHARINDEX(',',@Name,@begin) > 0
        begin
            set @temp=CHARINDEX(',',@Name,@begin)
            set @result=SUBSTRING(@Name,@begin,@temp-@begin)                
            set @begin=@temp+1
            insert into #test2(tempcol1) values(@result)    
        end
        set @real = SUBSTRING(@Name,@begin,len(@Name)-abs(@temp-@begin)+1)
                insert into #test2(tempcol1) values(@real)
    fetch next from split into @Name
End 
        select  distinct tempcol1 from #test2
Close split
Deallocate split
end
GO
--execution 
exec StringSplitIntoRows 'YourTableName','ColumnName'