Solved

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

Posted on 2015-01-21
12
215 Views
Last Modified: 2016-03-28
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
0
Comment
Question by:Hankinater
12 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 40562212
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
 
LVL 2

Author Comment

by:Hankinater
ID: 40564058
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
 
LVL 32

Expert Comment

by:awking00
ID: 40564134
Can there be more than two values?
0
 
LVL 2

Author Comment

by:Hankinater
ID: 40564140
If the server was rebooted 10 times there could be 10 values.
0
 
LVL 25

Expert Comment

by:jogos
ID: 40564671
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 25

Expert Comment

by:jogos
ID: 40565513
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
 
LVL 2

Author Comment

by:Hankinater
ID: 40580047
I could not get this to work - request that this be closed
0
 
LVL 2

Author Comment

by:Hankinater
ID: 40580669
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
 
LVL 25

Expert Comment

by:jogos
ID: 40580670
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
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 40585176
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41525177
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Solution for warm standby SQL server 20 34
Export import database 4 41
SQL Query Syntax Join 4 27
recover sqlserver db 8 45
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now