SQL Server query fields-CodePudding

I have a table that have been structured in the past like this, in my image below, the current structure of the table is like this, for a folder, you can have multiple agent.

My problem is that in the past, they store the id of each agent, comma separated. Today I want to do some queries but I need to parse of find a way to extract the data from the field AgentId, with an SQL query or subquery of CTE, anything SQL to achieve the result like in the image below in the What I want section. I tried to think something recursive with CTE, but I'm not that familiar with the recursive thing.

Please help me.

CodePudding user response：

string_split() in concert with a CROSS APPLY should do the trick.

Select FolderNumber
    , AgentID = B.value
From YourTable A
Cross Apply string_split(AgentID ,',') B

CodePudding user response：

You can use STRING_SPLIT () for those , cases and union them with other without , leftover results

Select folder_number, y.agent_id
from Table t
join STRING_SPLIT(
    Select agent_id 
    from table  
    Where agent_id like '%,%' and folder_number = t.folder_number, ','
) y on 1=1
Union
Select folder_number, agent_id
from Table
where agent_id not like '%,%'