Skip to content

Instantly share code, notes, and snippets.

@cwg999
Created June 15, 2018 20:44
Show Gist options
  • Save cwg999/3be80d6ef1ed9f3725e18fabd203fa05 to your computer and use it in GitHub Desktop.
Save cwg999/3be80d6ef1ed9f3725e18fabd203fa05 to your computer and use it in GitHub Desktop.
TSQL MSSQL Split Column into Rows
;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