Ok... first things first. Part of the reason why you've gotten no actual code to help you for the split portion of this problem is because you haven't posted readily consumable test data. Folks like to test their stuff before posting it and I'm no exception. I've converted your test data to readily consumable data to demonstrate with because you're new. I strongly recommend that you read the article at the first link in my signature line below under "Helpful Links" for future posts. You get tested, coded replies that way.

Thank you for posting your GetDomain splitter. As I suspected, it contains a slow While Loop to do the splits, not to mention that it's a Scalar Function rather than a high performance iTVF (Inline Table Valued Function).

Without further ado, take a trip over to the following article and get yourself a copy of the "DelimitedSplit8K" function from the "Resources" section near the bottom of the article. Once you've setup that function, this problem becomes child's play and will only be beat for performance by a CLR in the pre-2012 world.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Here's the code for a solution. Change column names and the table names to suit you. The result is stored in a Temp Table that's created on-the-fly. It also assumes the "IDx" at the beginning of each row of data is going to be unique. If it's not, there's a fix we can apply for that.

And, yes, I included a test table like you should in the future. You'll get tested answers much more quickly that way.

--===== Create the test table.

-- This is NOT a part of the solution. We're just creating test data here.

IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

DROP TABLE #TestTable

;

SELECT *

INTO #TestTable

FROM (

SELECT '234|2015-6-11|234aa|1' UNION ALL

SELECT 'ID1|a|A||234|ASDFASDF|2015-06-11|A|s||NULL|||||TEST|TEST|TEST|TEST|1543211' UNION ALL

SELECT 'ID2|B|A||234|ASDFASDF|2015-06-11|A|s||NULL|||||TEST|TEST|TEST|TEST|1234'

) d (PipeData)

;

--===== This does the split and repivot as well as creating and populating

-- the desired Temp Table on-the-fly.

SELECT C01 = SUBSTRING(tt.PipeData,1,CHARINDEX('|',tt.PipeData)-1)

,C02 = MAX(CASE WHEN split.ItemNumber = 2 THEN split.Item ELSE '' END)

,C03 = MAX(CASE WHEN split.ItemNumber = 3 THEN split.Item ELSE '' END)

,C04 = MAX(CASE WHEN split.ItemNumber = 4 THEN split.Item ELSE '' END)

,C05 = MAX(CASE WHEN split.ItemNumber = 5 THEN split.Item ELSE '' END)

,C06 = MAX(CASE WHEN split.ItemNumber = 6 THEN split.Item ELSE '' END)

,C07 = MAX(CASE WHEN split.ItemNumber = 7 THEN split.Item ELSE '' END)

,C08 = MAX(CASE WHEN split.ItemNumber = 8 THEN split.Item ELSE '' END)

,C09 = MAX(CASE WHEN split.ItemNumber = 9 THEN split.Item ELSE '' END)

,C10 = MAX(CASE WHEN split.ItemNumber = 10 THEN split.Item ELSE '' END)

,C11 = MAX(CASE WHEN split.ItemNumber = 11 THEN split.Item ELSE '' END)

,C12 = MAX(CASE WHEN split.ItemNumber = 12 THEN split.Item ELSE '' END)

,C13 = MAX(CASE WHEN split.ItemNumber = 13 THEN split.Item ELSE '' END)

,C14 = MAX(CASE WHEN split.ItemNumber = 14 THEN split.Item ELSE '' END)

,C15 = MAX(CASE WHEN split.ItemNumber = 15 THEN split.Item ELSE '' END)

,C16 = MAX(CASE WHEN split.ItemNumber = 16 THEN split.Item ELSE '' END)

,C17 = MAX(CASE WHEN split.ItemNumber = 17 THEN split.Item ELSE '' END)

,C18 = MAX(CASE WHEN split.ItemNumber = 18 THEN split.Item ELSE '' END)

,C19 = MAX(CASE WHEN split.ItemNumber = 19 THEN split.Item ELSE '' END)

,C20 = MAX(CASE WHEN split.ItemNumber = 20 THEN split.Item ELSE '' END)

INTO #SplitResult

FROM #TestTable tt

CROSS APPLY dbo.DelimitedSplit8k(tt.PipeData,'|') split

WHERE tt.PipeData LIKE 'ID%'

GROUP BY SUBSTRING(tt.PipeData,1,CHARINDEX('|',tt.PipeData)-1)

;

--===== Show that our data has been correctly split

SELECT * FROM #SplitResult

;

The "pivot" method that uses MAX(CASE)) that I used is an ancient but very fast method known as a CROSS TAB. You can read about that at the following link.

http://www.sqlservercentral.com/articles/T-SQL/63681/