SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


read multiple fields with common delimited data into either a temp table or table variable without...


read multiple fields with common delimited data into either a temp table or table variable without using cursors

Author
Message
mick burden
mick burden
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1006 Visits: 452
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62073 Visits: 17954
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 Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
saltpepo
saltpepo
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41098 Visits: 20000
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
mick burden
mick burden
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1006 Visits: 452
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')
saltpepo
saltpepo
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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,'|')
mick burden
mick burden
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1006 Visits: 452
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62073 Visits: 17954
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 Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62073 Visits: 17954
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 Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
mick burden
mick burden
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1006 Visits: 452
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')



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search