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
LVL 2
HankinaterAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jogosConnect With a Mentor Commented:
The question "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."
Inititialy answerd with a logical UDF-solution
http://#a40562212
With assisted solutions to handle the secondary concern that no permission will be granted to create udf in the original database
http://#a40564671
http://#a40565513
0
 
jogosCommented:
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

0
 
HankinaterAuthor Commented:
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
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
awking00Commented:
Can there be more than two values?
0
 
HankinaterAuthor Commented:
If the server was rebooted 10 times there could be 10 values.
0
 
jogosCommented:
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

0
 
jogosCommented:
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

0
 
HankinaterAuthor Commented:
I could not get this to work - request that this be closed
0
 
HankinaterAuthor Commented:
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.
0
 
jogosCommented:
The normal way to is that if you need a function you can create it,
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28600981.html#a40562212
not allowed by owner, no problem you can create it in other database
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28600981.html#a40564671
and a bonus-version without a function
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28600981.html#a40565513

"I couldn't t get it to work" is a little  short to know where 2 second options fail.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.