Re: [SQL ] 欄位字串分離統計的問題

看板Database (資料庫)作者funk6478 (大恩)時間3年前 (2021/02/25 11:40)推噓2(2推 0噓 0→)

留言2則, 2人參與討論串2/3 (看更多)

※ 引述《otherman (總經理)》之銘言： : 資料庫名稱： MSSQL : 資料庫版本： 2014 : 內容/問題描述： : table a : seq string : 1 a1,b1,c1 : 2 a2,c1 : 3 d3 : 4 a2,b1 : 5 z1,z2 : 將string欄位分離後計算數量，然後產出如下統計表 : a1 a2 a3 b1 b2 b3 c1 c2 c3...z1 z2 z3 : ===================================== : 1 2 0 2 0 0 2 0 0 1 1 0 : 我用pivot +string_spilt 來select做出來的結果都不是我想要的，因為欄位不確定 : 請問先進們有相關的統計經驗可以指點一下嗎？謝謝！ CREATE TABLE [#ttt] ( [seq] int, [string] nvarchar(4000) ) INSERT INTO #ttt ([seq],[string]) VALUES (1,'a1,b1,c1'), (2,'a2,c1'), (3,'d3'), (4,'a2,b1'), (5,'z1,z2') --SELECT * FROM #ttt CREATE TABLE [#tttt] ( [string_all] nvarchar(4000) ) INSERT INTO #tttt ([string_all]) VALUES ('a1'), ('a2'), ('a3'), ('b1'), ('b2'), ('b3'), ('c1'), ('c2'), ('c3'), ('z1'), ('z2'), ('z3') --SELECT * FROM #tttt DECLARE @UGG NVARCHAR(MAX) DECLARE @UHH NVARCHAR(MAX) DECLARE @UHHH NVARCHAR(MAX) SELECT @UGG = COALESCE(@UGG + ',','') + [string] FROM (SELECT [string] from #ttt) as GG SELECT @UHH = '(''' + replace(@UGG,',','''),(''') + ''')' SELECT @UHHH = '(''''' + replace(@UGG,',','''''),(''''') + ''''')' DECLARE @uuu AS nvarchar(4000) SELECT @uuu = N' DECLARE @ggg AS TABLE([string_new] nvarchar(4000)) INSERT INTO @ggg VALUES ' + @UHH + N' DECLARE @UNIC NVARCHAR(MAX) SELECT @UNIC = COALESCE(@UNIC + '','','''') + QUOTENAME([string_new]) from (SELECT DISTINCT TOP 1000 [string_new] FROM @ggg ORDER BY [string_new] ) as QQ DECLARE @UNICT NVARCHAR(MAX) SELECT @UNICT = COALESCE(@UNICT + '','','''') + QUOTENAME([string_all]) from (SELECT DISTINCT TOP 1000 [string_all] FROM #tttt ORDER BY [string_all] ) as QQ DECLARE @UTEXT NVARCHAR(MAX) SELECT @UTEXT = N'' DECLARE @gggg AS TABLE([string_new] nvarchar(4000)) INSERT INTO @gggg VALUES ' + @UHHH + N' SELECT * FROM ( SELECT [string_all] ,case when [C] = 1 then 1 else 0 end [CG] FROM #tttt left join ( SELECT [string_new] ,1 as [C] FROM @gggg ) as tg on [string_all] = [string_new] ) as FF PIVOT ( SUM([CG]) for [string_all] in ('' + @UNICT + N'')) as YY '' EXEC sp_executesql @UTEXT ' --select @uuu EXEC sp_executesql @uuu DROP TABLE #ttt DROP TABLE #tttt -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 140.112.207.182 (臺灣) ※ 文章網址: https://www.ptt.cc/bbs/Database/M.1614224439.A.FEE.html