表
M1 M2 M3 M4 M5
12 13 3 1 2
12 23 1 123 123
...
紀錄有幾萬行，怎樣快速拆分資料
每一欄位取一個數字
比如第一行可以拆分為2*2*1*1*1=4條紀錄：
M1 M2 M3 M4 M5
1 1 3 1 2
1 3 3 1 2
2 1 3 1 2
2 3 3 1 2
第二行可以拆分為2*2*1*3*3=36條紀錄。
這樣拆分所有的紀錄后得到一個新表。
哪位大神能夠處理，謝謝啦！！
uj5u.com熱心網友回復：
沒看懂。你先解釋一下，第一行的M1、M2是分別是12，13
而第二行是12，23。
怎么拆成的：1、1和1、3？這是個什么規則。
uj5u.com熱心網友回復：
CREATE TABLE #T
(M1 INT,
M2 INT,
M3 INT,
M4 INT,
M5 INT)
INSERT INTO #T
SELECT 12,13,3,1,2 UNION ALL
SELECT 12,23,1,123,123
SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN INTO #T1 FROM #T
SELECT RN,VAL,COL,SUBSTRING(VAL,number,1) AS SINGLE_CHAR
INTO #T2
FROM
(SELECT RN,CAST(VAL AS VARCHAR(10)) AS VAL,COL FROM #T1
UNPIVOT (VAL FOR COL IN ([M1],[M2],[M3],[M4],[M5])) AS B) AS C
JOIN master.dbo.spt_values D on number<=LEN(val)
WHERE TYPE='P' AND number>0
SELECT A.RN,M1,M2,M3,M4,M5
FROM
(SELECT RN,SINGLE_CHAR AS M1 FROM #T2 WHERE COL='M1') AS A
JOIN
(SELECT RN,SINGLE_CHAR AS M2 FROM #T2 WHERE COL='M2') AS B ON A.RN=B.RN
JOIN
(SELECT RN,SINGLE_CHAR AS M3 FROM #T2 WHERE COL='M3') AS C ON A.RN=C.RN
JOIN
(SELECT RN,SINGLE_CHAR AS M4 FROM #T2 WHERE COL='M4') AS D ON A.RN=D.RN
JOIN
(SELECT RN,SINGLE_CHAR AS M5 FROM #T2 WHERE COL='M5') AS E ON A.RN=E.RN
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/283620.html
標籤：疑難問題
上一篇：關于ssm整合，搞了一堆bug
下一篇：求求求