作者funk6478 (大恩)
看板Database
標題Re: [SQL ] 欄位字串分離統計的問題
時間Thu Feb 25 11:40:37 2021
※ 引述《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://webptt.com/m.aspx?n=bbs/Database/M.1614224439.A.FEE.html
1F：推 otherman: 謝謝f大提供這麼詳細的解說…我先試一下，感謝！ 02/25 15:59
2F：推 cutekid: 推(Y)，學習+1！ 02/26 18:40