Excel列值(不限字符串数字)转换Vaules()拼凑表

 

DECLARE @TaskCmdOne VARCHAR(MAX)='北京
上海
广州
长春
济南
成都
南京
武汉', @TaskCmdTwo VARCHAR(MAX)


SELECT @TaskCmdOne=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@TaskCmdOne,'，',','),'、',','),'/',','),'\',','),'|',',')

IF CHARINDEX(',',@TaskCmdOne)=0 
BEGIN
  SELECT @TaskCmdOne=REPLACE(@TaskCmdOne,CHAR(13)+CHAR(10),',')
  WHILE LEFT(@TaskCmdOne,1)=',' SELECT @TaskCmdOne=RIGHT(@TaskCmdOne,LEN(@TaskCmdOne)-1)
  WHILE RIGHT(@TaskCmdOne,1)=',' SELECT @TaskCmdOne=LEFT(@TaskCmdOne,LEN(@TaskCmdOne)-1)
END ELSE
BEGIN
    SELECT @TaskCmdOne=REPLACE(@TaskCmdOne,CHAR(13)+CHAR(10),'<br>') 
    SELECT @TaskCmdOne=REPLACE(@TaskCmdOne,'<br>','')
END

SELECT @TaskCmdOne=REPLACE(@TaskCmdOne,' ','')
SELECT @TaskCmdTwo=REPLACE(@TaskCmdOne,',',''')'+','+'(''')
SELECT @TaskCmdOne=REPLACE(@TaskCmdOne,',',''''+','+'''')

SELECT @TaskCmdOne=''''+@TaskCmdOne+''''
SELECT @TaskCmdTwo='('''+@TaskCmdTwo+''')'
SELECT @TaskCmdOne=REPLACE(@TaskCmdOne,' ','')
SELECT @TaskCmdTwo=REPLACE(@TaskCmdTwo,' ','')

DECLARE @xml XML,@xmltwo XML
SELECT @xml=CAST('<x>'+REPLACE(@TaskCmdOne,',',',</x><x>')+'</x>' AS XML) 
SELECT @xmltwo=CAST('<x>'+REPLACE(@TaskCmdTwo,',',',</x><x>')+'</x>' AS XML) 

SELECT A.RowID,A.col1,B.col2 FROM (
SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) RowID,T.C.value('.','varchar(500)') AS col1  from  
(SELECT @xml.query('.') AS name) AS a
CROSS APPLY a.name.nodes('/x') T(C))A INNER JOIN (
SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) RowID,T.C.value('.','varchar(500)') AS col2  from  
(SELECT @xmltwo.query('.') AS name) AS a
CROSS APPLY a.name.nodes('/x') T(C))B ON A.RowID=B.RowID