Solved

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

Posted on 2015-01-21
12
214 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 31

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

760 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

24 Experts available now in Live!

Get 1:1 Help Now