Solved

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

Posted on 2015-01-21
12
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
MongoDB Through a MySQL Lens

This article looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.

 
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
 
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 66

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

628 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