SQL 数据列转行处理

 

input：1096890,1077021,1095605,1095589,1093351,1095609,1095609,1083844,1096890

DECLARE    @ComboID    VARCHAR(1000)
SET        @ComboID = '1096890,1077021,1095605,1095589,1093351,1095609,1095609,1083844,1096890' 

SET NOCOUNT ON
 
CREATE TABLE #TEMP_00(
            ComboID VARCHAR(1000))
            

INSERT INTO    #TEMP_00 
SELECT        @ComboID
 

SELECT        RIGHT(RTRIM(LTRIM(B.ComboID)),10) AS ComboID
INTO        #TEMP_01
FROM        (SELECT    ComboID = CONVERT(XML,
                    '<root><v>' + REPLACE(ComboID, ',', '</v><v>') + '</v></root>')
            FROM #TEMP_00)A
OUTER APPLY(SELECT ComboID = N.v.value('.', 'varchar(100)')
            FROM A.ComboID.nodes('/root/v') N(v))B
            
SELECT    *
FROM    #TEMP_01

 

result:

1096890
1077021
1095605
1095589
1093351
1095609
1095609
1083844
1096890