Created
June 15, 2018 20:44
-
-
Save cwg999/3be80d6ef1ed9f3725e18fabd203fa05 to your computer and use it in GitHub Desktop.
TSQL MSSQL Split Column into Rows
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
;with cte (ID, DeclarationItem, Declaration,SEQM) as | |
( | |
select ID, | |
cast(LEFT(CustomText01, 20) as varchar(20)) DeclarationItem, | |
RIGHT(CustomText01, | |
CASE | |
WHEN LEN(CustomText01)-20 > 0 THEN LEN(CustomText01)-20 | |
ELSE 0 | |
END | |
) Declaration, | |
1 | |
from dbo.DocumentHeaders | |
union all | |
select ID, | |
cast(left(Declaration, 20) as varchar(20)) DeclarationItem, | |
RIGHT(Declaration, | |
CASE | |
WHEN LEN(Declaration)-20 > 0 THEN LEN(Declaration)-20 | |
ELSE 0 | |
END | |
) Declaration, | |
SEQM+1 | |
from cte | |
where Declaration > '' | |
) | |
select A.ID | |
, SEQM | |
, DeclarationItem | |
--, ROW_NUMBER() OVER (ORDER BY A.ID) AS ROW | |
-- , LEN(CustomText01) as TXTLEN | |
from cte A | |
-- WHERE A.ID IN ('5019','15876') | |
LEFT JOIN dbo.DocumentHeaders B | |
ON A.ID = B.ID | |
-- ORDER BY TXTLEN DESC,ID,SEQM | |
ORDER BY ID,SEQM |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment