The first two days saw a blog post about how to convert a row of data that contains a single column of multiple values into a multi-row column in Oracle, with the following issues.
ID number
1 137xxxx,138xxxx
Take it out.
ID number
1 137xxxx
1 138xxxx
The solution given in the original text can be found in http://www.cnblogs.com/myjoan/p/4139348.html, and the reply seems to give a more concise wording.
Because for several years did not touch Oracle, in recent years have been using Microsoft's SQL SEVER, so I think if it is SQL SERVER, how should solve this problem? All I could think of was a recursive approach, so I used the CTE table.
The code is as follows
withTest as (SELECT 1Id'13811111111,13311111111,13900000000'Phone--From dualUNION AllSELECT 2,'15811111111,15911111111,18800000000' --From dual), CTE as(SelectId,phone,SUBSTRING(Phone,1,LEN(phone)- CHARINDEX(',',REVERSE(phone))) Leftphone,SUBSTRING(Phone,LEN(phone)- CHARINDEX(',',REVERSE(phone))+2,CHARINDEX(',',REVERSE(phone))) Newphone fromTestUnion AllSelectId,leftphone,SUBSTRING(Leftphone,1,LEN(Leftphone)- CHARINDEX(',',REVERSE(Leftphone))) Leftphone,SUBSTRING(Leftphone,LEN(Leftphone)- CHARINDEX(',',REVERSE(Leftphone))+2,CHARINDEX(',',REVERSE(Leftphone))) Newphone fromCTEwhereNewphone<>"')SelectId Case whenNewphone="' ThenLeftphoneElseNewphoneEnd asPhone fromCTEOrder by 1
I wonder if there are any other ways to solve this problem? Also please share mutual encouragement, thank you!
Implementation of single-row multi-valued data into multiple rows in SQL Server