M1, M2, M3, M4, M5
12 13 1 2 3
12 and 1 123 123
.
With tens of thousands of lines, record how fast split data
Every field take a digital
Such as the first line can be broken down into 2 * 2 * 1 * 1 * 1=4 record:
M1, M2, M3, M4, M5
1 1 1 2 3
1 3 3 1 2
1 2 3 1 2
2, 3, 3 1 2
The second line can be broken down into 2 * 2 * 1 * 3 * 3=36 records,
This split all record after receive a new table,
What a great god can handle, thank you!!
CodePudding user response:
Didn't understand,First you explain, is the first line of the M1, M2, respectively is 12, 13
And the second line is June 12,
How to split into: 1, 1 and 1, 3? What is this rule,
CodePudding user response:
The CREATE TABLE # T
(M1 INT,
M2 INT,
The M3 INT,
The M4 INT,
The M5 INT)
INSERT INTO # T
The SELECT 12,13,3,1,2 UNION ALL
The SELECT 12,23,1,123,123
SELECT *, ROW_NUMBER () OVER (ORDER BY 1) (SELECT) AS an RN # INTO T1 FROM # T
SELECT an RN, VAL, COL, SUBSTRING (VAL, number, 1) AS SINGLE_CHAR
INTO # T2
The FROM
(SELECT an RN, CAST (VAL AS VARCHAR (10)) AS VAL, COL # FROM T1
UNPIVOT (VAL FOR COL (IN (M1), [M2], [M3], [the M4], [] M5)) AS B) AS C
JOIN the master. The dbo. Spt_values D on number<=LEN (val)
WHERE TYPE='P' AND number> 0
The SELECT A.R N, M1, M2, M3, M4, M5
The FROM
(SELECT an RN, SINGLE_CHAR AS M1 FROM # T2 WHERE COL='M1') AS A
The JOIN
(SELECT an RN, SINGLE_CHAR AS M2 FROM # T2 WHERE COL='M2') AS B ON A.R N=B.R N
The JOIN
(SELECT an RN, SINGLE_CHAR AS M3 FROM # T2 WHERE COL='M3') AS C ON A.R N=C.R N
The JOIN
(SELECT an RN, SINGLE_CHAR AS M4 # FROM T2 WHERE COL='M4') AS D ON A.R N=D.R N
The JOIN
(SELECT an RN, SINGLE_CHAR AS M5 FROM # T2 WHERE COL='M5') AS E ON A.R N="E.R N