SQLSERVER七夕活动导数据【列转行的应用】

SELECT A.BookID,A.BookName+'                                             ',+B.TOTALCOUNT FROM NOVEL_BOOK A, (
SELECT RESID,COUNT(1) AS TOTALCOUNT FROM (
select  SUBSTRING(','+b.ChapterIDInfo+',',CHARINDEX(',',','+b.ChapterIDInfo+',',a.number)+1,CHARINDEX(',',','+b.ChapterIDInfo+',',a.number+1)-CHARINDEX(',',','+b.ChapterIDInfo+',',a.number)-1)
AS RESID
FROM (SELECT * from PandaCoinOrder_BulkPayChapter WHERE bookid=15)  b

left join master..spt_values a on CHARINDEX(',',','+b.ChapterIDInfo+',',a.number) = a.number

where a.type='p'

and CHARINDEX(',',','+b.ChapterIDInfo+',',a.number+1)-CHARINDEX(',',','+b.ChapterIDInfo+',',a.number)>0
) AS MYTABLE  GROUP BY RESID) B WHERE A.BookID=B.RESID

备注： 这里是将一列中数据有【逗号】分隔的数据，转成多行数据，并进行分析