Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » read multiple fields with common delimited...
Add to briefcase
13 posts, Page 1 of 2
12»»

read multiple fields with common delimited data into either a temp table or table variable without using cursors Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
mick burden
Posted Monday, September 24, 2012 7:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 31, 2013 12:57 AM
Points: 244, Visits: 301
if I have a table with fields which contain common delimited data in them, is there a way I can read them into some kind of temporary table, my example might help me explain what I'm after

[code=plain]

field1 .............. field2................. field3........................... field4
24/Sep/2012.......jim|frank|sue.......Alan|Tom|George...........Alice|Tony|Mike
25/Sep/2012.......Tony|Steph|Al.....etc
[/code]

I'd like to be able to query this table by date, so if I put in select where field1 = '24/Sep/2012' it would produce the data in one field like

jim
frank
sue
Alan
Tom
George
... etc

without using cursors, is this possible?

Sorry for the bad formatting, not sure how to type it in here
Post #1363472
Sean Lange
Posted Monday, September 24, 2012 8:37 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 8,980, Visits: 8,540
mick burden (9/24/2012)
if I have a table with fields which contain common delimited data in them, is there a way I can read them into some kind of temporary table, my example might help me explain what I'm after

[code=plain]

field1 .............. field2................. field3........................... field4
24/Sep/2012.......jim|frank|sue.......Alan|Tom|George...........Alice|Tony|Mike
25/Sep/2012.......Tony|Steph|Al.....etc
[/code]

I'd like to be able to query this table by date, so if I put in select where field1 = '24/Sep/2012' it would produce the data in one field like

jim
frank
sue
Alan
Tom
George
... etc

without using cursors, is this possible?

Sorry for the bad formatting, not sure how to type it in here


Can't provide much help with the coding because there is nothing to work with here. If you need help with the actual code you need to look at the first link in my signature about best practices when posting.

To parse your string you should take a look at the article in my signature about splitting strings. The logic found there is what you are ultimately going to need to solve this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1363533
saltpepo
Posted Tuesday, September 25, 2012 2:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 6:21 AM
Points: 16, Visits: 64
Might be u get hel with dis.

CREATE TABLE dbo.Test
(
id int,
create_dt DATETIME NOT NULL,
Field1 NVARCHAR(100) NOT NULL,
Field2 NVARCHAR(2500) NOT NULL

);
GO

Insert Test
Select '1','24/Sep/2012','.jim|frank|sue','Alan|Tom|George' union all
Select '2','25/Sep/2012','jim|frank|sue','jim|frank|sue'
Insert Test
Select '1','24/Sep/2012','.jim|frank|sue','Alan|Tom|George' union all
Select '2','25/Sep/2012','jim|frank|sue','jim|frank|sue'
GO
Declare @asd nvarchar(100)
select @asd=Field1 from Test where create_dt='24/Sep/2012'

Select Data as Field1 from Split(@asd,'|')

CreateFUNCTION [dbo].[Split]
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END
Post #1363865
ChrisM@Work
Posted Tuesday, September 25, 2012 3:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140
mick burden (9/24/2012)
if I have a table with fields which contain common delimited data in them, is there a way I can read them into some kind of temporary table, my example might help me explain what I'm after

[code=plain]

field1 .............. field2................. field3........................... field4
24/Sep/2012.......jim|frank|sue.......Alan|Tom|George...........Alice|Tony|Mike
25/Sep/2012.......Tony|Steph|Al.....etc
[/code]

I'd like to be able to query this table by date, so if I put in select where field1 = '24/Sep/2012' it would produce the data in one field like

jim
frank
sue
Alan
Tom
George
... etc

without using cursors, is this possible?

Sorry for the bad formatting, not sure how to type it in here


As Sean points out, the data is a bit vague. Is it something like this?

SELECT field1, field2, field3, field4
FROM (
	SELECT 
		field1 = CAST('24/Sep/2012' AS DATETIME), 
		field2 = 'jim|frank|sue', 
		field3 = 'Alan|Tom|George', 
		field4 = 'Alice|Tony|Mike' UNION ALL
	SELECT '25/Sep/2012', 'Tony|Steph|Al', NULL, NULL
) d

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1363875
mick burden
Posted Tuesday, September 25, 2012 3:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 31, 2013 12:57 AM
Points: 244, Visits: 301
My appologies, I've now created a script to make a table containing the data... I hope this helps

USE test
GO
-- Creating Test Table
CREATE TABLE testtable(field1 datetime, field2 VARCHAR(400), field3 VARCHAR(400),field4 VARCHAR(400))
GO
-- Inserting Data into Table
INSERT INTO testtable(field1,field2,field3,field4)
VALUES('24/Sep/2012','jim|frank|sue','Alan|Tom|George','Alice|Tony|Mike')

INSERT INTO testtable(field1,field2,field3,field4)
VALUES('25/Sep/2012','Tony|Steph|Al','Alice|Tony|Mike','Reg|Steve|John')
Post #1363885
saltpepo
Posted Tuesday, September 25, 2012 3:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 6:21 AM
Points: 16, Visits: 64
Using function u can get desired result without cursors.I created split function above use that.

Declare @asd nvarchar(100)
select @asd=Field1 from Test where create_dt='24/Sep/2012'

Select Data as Field1 from Split(@asd,'|')
Post #1363891
mick burden
Posted Tuesday, September 25, 2012 7:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 31, 2013 12:57 AM
Points: 244, Visits: 301
That works well, thank you, but I need it to go across all the fields at once, adding all the data to a single table or recordset
Post #1364038
Sean Lange
Posted Tuesday, September 25, 2012 7:47 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 8,980, Visits: 8,540
saltpepo (9/25/2012)
Using function u can get desired result without cursors.I created split function above use that.

Declare @asd nvarchar(100)
select @asd=Field1 from Test where create_dt='24/Sep/2012'

Select Data as Field1 from Split(@asd,'|')



That function will work but it is not as good for performance. You are using a while loop which is pretty much the same thing as a cursor. Take a look at the solution at the link in my signature about splitting strings. It will blow the doors off the while loop approach for performance.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1364044
Sean Lange
Posted Tuesday, September 25, 2012 7:49 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 8,980, Visits: 8,540
mick burden (9/25/2012)
That works well, thank you, but I need it to go across all the fields at once, adding all the data to a single table or recordset


So then for the second time in this thread, if you need specific coding help you need to post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. The first link in my signature will help you put that all together. Without this we are just shooting in the dark. Help us help you and you will be rewarded with tested, accurate and fast code.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1364046
mick burden
Posted Tuesday, September 25, 2012 8:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 31, 2013 12:57 AM
Points: 244, Visits: 301
is the following that I put in a thread earlier not good enough? it created a table with the desired data inputted. What I wanted is an output that gave me the following. I wanted the data for 24/Sep/2012
jim
frank
sue
Alan
Tom
George
Alice
Tony
Mike



GO
-- Creating Test Table
CREATE TABLE testtable(field1 datetime, field2 VARCHAR(400), field3 VARCHAR(400),field4 VARCHAR(400))
GO
-- Inserting Data into Table
INSERT INTO testtable(field1,field2,field3,field4)
VALUES('24/Sep/2012','jim|frank|sue','Alan|Tom|George','Alice|Tony|Mike')

INSERT INTO testtable(field1,field2,field3,field4)
VALUES('25/Sep/2012','Tony|Steph|Al','Alice|Tony|Mike','Reg|Steve|John')
Post #1364073
« Prev Topic | Next Topic »

Add to briefcase
13 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.