22,207
社区成员
发帖
与我相关
我的任务
分享
DROP TABLE IF EXISTS dbo.dqdm
CREATE TABLE dbo.dqdm (
ID INT identity(1,1) NOT NULL primary key,
string varchar(500) not null
);
-- ----------------------------
-- 插入示例数据
-- ----------------------------
INSERT INTO dbo.dqdm(string) VALUES ('杭州:分类2:3:20130725;宁波:分类1:20:20130601');
INSERT INTO dbo.dqdm(string) VALUES ('温州:分类3:1:20160701');
INSERT INTO dbo.dqdm(string) VALUES ('丽水:分类2:12:20160501');
INSERT INTO dbo.dqdm(string) VALUES (':分类1:3:20160725');
INSERT INTO dbo.dqdm(string) VALUES ('温州:分类1:2:20160401');
INSERT INTO dbo.dqdm(string) VALUES ('宁波:分类2::20160411');
INSERT INTO dbo.dqdm(string) VALUES ('杭州:分类2:3:20160725');
INSERT INTO dbo.dqdm(string) VALUES ('丽水:分类1:14:');
INSERT INTO dbo.dqdm(string) VALUES ('舟山:分类2:3:20160201');
INSERT INTO dbo.dqdm(string) VALUES ('绍兴:分类1:3:20160301');
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T
(AREA VARCHAR(20),
TYPE VARCHAR(10),
QTY VARCHAR(10),
DATE_TIME DATE)
SELECT A.*,B.number,
SUBSTRING(string,number,CHARINDEX(';',STRING+';',NUMBER)-NUMBER) AS SINGLE_STRING
INTO #A
FROM dqdm A
JOIN master.dbo.spt_values B ON CHARINDEX(';',';'+string,NUMBER)=number
WHERE TYPE='P'
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL=ISNULL(@SQL+' UNION ALL ','')+'SELECT '''+REPLACE(SINGLE_STRING,':',''',''')+''''
FROM #A
WHERE CHARINDEX(':',SINGLE_STRING)<>1
INSERT INTO #T
EXEC(@SQL)
DROP TABLE #A
SELECT *,CASE WHEN DATE_TIME='1900-01-01' THEN GETDATE() ELSE DATE_TIME END FROM #T