CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
MattAtWork
MattAtWork

Reputation: 3

How to split a multi-valued, character delimited column into multiple rows?

Long time lurker, first time question asker...

I haven't found an answer to this specific problem though it seems familiar I'm sure.

I have a table with a column containing a semi-colon delimited list of values. The table is a list of applications along with the servers associated with that application. The list of servers is semi-colon delimited but is separated into serverId;serverName for x number of servers - there are some applications associated with hundreds of servers, some are associated with 1, and some are associated with 0. I would like to separate this servers column into rows for each unique server including the Id from the table as well as the serverId and serverName as columns.

Data currently looks like this

applicationId\Servers 1\serverId1;serverName1 2\serverId2;serverName2;serverId3;serverName3 3\serverId1;serverName1;serverId5;serverName5;serverId8;serverName8 4\serverId9;serverName9 5\

I'd like to create a new table with the applicationId, serverId, and serverName columns, like this:

applicationId\serverId\serverName 1\serverId1\serverName1 2\serverId2\serverName2 2\serverId3\serverName3 3\serverId1\serverName1 3\serverId5\serverName5 3\serverId8\serverName8 4\serverId9\serverName9 5\

Alternatively, if I could just get the serverId into a table with the the applicationId it would be helpful as I do have a table with serverId and serverName, as well.

applicationId\serverId

I have tried splitting the column on just ';' but that produces not very helpful output like the following:

applicationId\serverColumn 1\serverId1 1\serverName1 2\serverId2 2\serverName2 2\serverId3 2\serverName3 ....

Any help would be greatly appreciated.

Upvotes: 0

Views: 50

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

This is just an awful data structure to say the least. But it is possible using Jeff Moden's splitter. http://www.sqlservercentral.com/articles/Tally+Table/72993/ I know you say you have a splitter but if it has a loop or recursive cte you need to throw it away for a set based one like Jeff's.

Here is a fully functional example of how you could leverage his splitter to make this work.

declare @Something table 
(
    ApplicationID int
    , ServerInfo varchar(100))
insert @Something 
(
    ApplicationID
    , ServerInfo
) values
(1, 'serverId1;serverName1')
, (2, 'serverId2;serverName2;serverId3;serverName3')
, (3, 'serverId1;serverName1;serverId5;serverName5;serverId8;serverName8')
, (4, 'serverId9;serverName9')
;

with ServerIDs as
(
    select s.ApplicationID
        , ServerID = case when x.ItemNumber % 2 = 1 then x.Item end
        , x.ItemNumber
    from @Something s
    cross apply dbo.DelimitedSplit8K(s.ServerInfo, ';') x
    where x.ItemNumber % 2 = 1
)
, ServerNames as
(
    select s.ApplicationID
        , ServerName = case when x.ItemNumber % 2 = 0 then x.Item end
        , x.ItemNumber
    from @Something s
    cross apply dbo.DelimitedSplit8K(s.ServerInfo, ';') x
    where x.ItemNumber % 2 = 0
)

select si.ApplicationID
    , si.ServerID
    , sn.ServerName
from ServerIDs si
join ServerNames sn on sn.ApplicationID = si.ApplicationID and si.ItemNumber + 1 = sn.ItemNumber

This returns:

ApplicationID   ServerID    ServerName
1               serverId1   serverName1
2               serverId2   serverName2
2               serverId3   serverName3
3               serverId1   serverName1
3               serverId5   serverName5
3               serverId8   serverName8
4               serverId9   serverName9

Upvotes: 2

Related Questions