asked on

T-SQL query to split one record into multiple with comma delimited column data

Hey Experts...

This one is kind of confusing to explain. I have a SQL view that returns data regarding servers that were rebooted in the last 24 hours.

One of the column's data can have more than one value separated by commas. So one of the records that is returned would look like this:

ServerName: Server01
LastRebooted: DomainName/UserName 01/20/2015 16:55:05, DomainName/UserName 01/20/2015 13:19:13
Domain: DomainName

The LastRebooted has two different times it was rebooted.

Is there a way to split that out into two records using a SELECT query? So basically this record would return two different items in the results.

[ServerName], [LastRebooted],[Domain]
Server01, DomainName/UserName 01/20/2015 16:55:05, DomainName
Server01, DomainName/UserName 01/20/2015 13:19:13, DomainName

jogos

Use a function to split the string and cross apply with it

How to split
http://www.codeproject.com/Tips/798671/Split-string-into-Rows-Columns-using-Delimiters

select t1.*,x.items 
from yourtab t1
cross apply dbo.split(t1.details,';') x;

Open in new window

Hankinater

ASKER

Thanks for the response jogos.

I don't have permissions to create functions and the database owner will not allow the function to be created. I guess I will have to come up with another way to present the data.

Hank

awking00

Can there be more than two values?

Hankinater

ASKER

If the server was rebooted 10 times there could be 10 values.

jogos

I don't have permissions to create functions and the database owner will not allow the function to be created. I guess I will have to come up with another way to present the data.

That udf does not forcely be created in the database of the owner, if you have a utility-db where you can create that udf then you can call that from there.

select t1.*,x.items 
from yourtab t1
cross apply UTILDB.dbo.split(t1.details,';') x;

Open in new window

jogos

Or a CTE-way

create table Bootlog (ServerName varchar(20)
,LastRebooted varchar(max),Domain varchar(20))

insert bootlog
select  'Server01','DomainName/UserName 01/20/2015 16:55:05, DomainName/UserName 01/20/2015 13:19:13','DomainName'
union all select  'Server02','DomainName/UserName 01/20/2015 18:19:18','DomainName'
union all select  'Server02','DomainName/UserName 01/20/2015 16:55:05','DomainName'

;with tmp (servername,domain,text,lastrebooted) as (
select servername,domain,
 LEFT(lastrebooted, CHARINDEX(',',lastrebooted+',')-1),
    STUFF(lastrebooted, 1, CHARINDEX(',',lastrebooted+','), '')
from bootlog
union all
select servername,domain,
 LEFT(lastrebooted, CHARINDEX(',',lastrebooted+',')-1),
    STUFF(lastrebooted, 1, CHARINDEX(',',lastrebooted+','), '')
from tmp
where lastrebooted > ''
  )
select * from tmp;

Open in new window

Hankinater

ASKER

I could not get this to work - request that this be closed

Hankinater

ASKER

I've requested that this question be deleted for the following reason:

The solutions provided did not work for me. The customer is going a different route to get the data formatted the way they want it. Request that the question be deleted.

jogos

The normal way to is that if you need a function you can create it,
https://www.experts-exchange.com/questions/28600981/T-SQL-query-to-split-one-record-into-multiple-with-comma-delimited-column-data.html?anchorAnswerId=40562212#a40562212
not allowed by owner, no problem you can create it in other database
https://www.experts-exchange.com/questions/28600981/T-SQL-query-to-split-one-record-into-multiple-with-comma-delimited-column-data.html?anchorAnswerId=40564671#a40564671
and a bonus-version without a function
https://www.experts-exchange.com/questions/28600981/T-SQL-query-to-split-one-record-into-multiple-with-comma-delimited-column-data.html?anchorAnswerId=40565513#a40565513

"I couldn't t get it to work" is a little short to know where 2 second options fail.

ASKER CERTIFIED SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

All - I just kicked out an article on this situation called T-SQL: Normalized data to a single comma delineated string and back, if you like it please click on the 'Good Article' button at the bottom.