asked
Export data into multiple columns
I found this script online and looking to enahance it. I am having issues understanding on how this works. Would anyone else have a way of helping me? I am looking to expand the script to go into 10 columns instead of the original 3 it was provided in. DECLARE @tab TABLE ( String VARCHAR(100) ) INSERT INTO @tab SELECT 'Test|Test2|Test3||Test4|Test5|Test6|Test7|Test8|Test9|Test10|Test11' UNION ALL SELECT 'Test|Test2|Test3||Test4|Test5|Test6|Test7|Test8|Test9|Test10|Test11' SELECT s.Col1 , t.Col2 , u.Col3 FROM @tab CROSS APPLY ( SELECT CHARINDEX('|', String) AS p1 ) p CROSS APPLY ( SELECT SUBSTRING(String, 1, p.p1 - 1) AS Col1 ) s CROSS APPLY ( SELECT CHARINDEX('|', String, p.p1 + 1) AS p2 ) q CROSS APPLY ( SELECT SUBSTRING(String, p.p1 + 1, q.p2 - p.p1 - 1) AS Col2 ) t CROSS APPLY ( SELECT SUBSTRING(String, q.p2 + 1, LEN(String)) AS Col3 ) u
Here's an XML based solution that will do it. Full credit to
http://forums.asp.net/t/1965445.aspx?How+to+split+a+comma+separated+value+to+columns+in+sql+server, where I saw the idea for this. DECLARE @tab TABLE ( String VARCHAR(100) ) INSERT INTO @tab SELECT 'Test|Test2|Test3|Test4|Test5|Test6|Test7|Test8|Test9|Test10|Test11' UNION ALL SELECT 'Test|Test2|Test3|Test4|Test5|Test6|Test7|Test8|Test9|Test10|Test11'; WITH Split_Names AS -- Define the CTE query. ( SELECT String, CONVERT(XML, ' ' + REPLACE(String, '|', '') + ' ') AS xmlname FROM @tab T ) -- Define the outer query referencing the CTE name. SELECT String, xmlname.value('/Cols[1]/col[1]', 'varchar(100)') AS Col1, xmlname.value('/Cols[1]/col[2]', 'varchar(100)') AS Col2, xmlname.value('/Cols[1]/col[3]', 'varchar(100)') AS Col3, xmlname.value('/Cols[1]/col[4]', 'varchar(100)') AS Col4, xmlname.value('/Cols[1]/col[5]', 'varchar(100)') AS Col5, xmlname.value('/Cols[1]/col[6]', 'varchar(100)') AS Col6, xmlname.value('/Cols[1]/col[7]', 'varchar(100)') AS Col7, xmlname.value('/Cols[1]/col[8]', 'varchar(100)') AS Col8, xmlname.value('/Cols[1]/col[9]', 'varchar(100)') AS Col9, xmlname.value('/Cols[1]/col[10]', 'varchar(100)') AS Col10, xmlname.value('/Cols[1]/col[11]', 'varchar(100)') AS Col11 FROM Split_Names
16 People are following this question.
