Littlet5621
asked on
Convert Comma Seporated Column Into Rows in SQL
I have a code that will do what will do this the problem is it has a limit of 100 comma separate field that it can do. I need on that handle anywhere from 1 to 500 or more.
Here is the code that i am currently useing.
Attached is a example of how the data looksexample.xlsx
Here is the code that i am currently useing.
;with tmp(VEND_ITEM_NO, MFG, MODEL, Data) as (
select VEND_ITEM_NO, MFG, LEFT(MODEL, CHARINDEX(',',MODEL+',')-1),
STUFF(MODEL, 1, CHARINDEX(',',MODEL+','), '')
from USER_TEMP_TABLE1 where MFG in ('SONY')
union all
select VEND_ITEM_NO, MFG, LEFT(Data, CHARINDEX(',',Data+',')-1),
STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
)
select VEND_ITEM_NO, MFG, MODEL
from tmp
order by VEND_ITEM_NO
Attached is a example of how the data looksexample.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window