Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

read multiple fields with common delimited data into either a temp table or table variable without using cursors Expand / Collapse
Author
Message
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: Thursday, August 14, 2014 5:23 AM
Points: 287, Visits: 405
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
Posted Monday, September 24, 2012 8:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1363533
Posted Tuesday, September 25, 2012 2:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 2, 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
Posted Tuesday, September 25, 2012 3:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 7,208, Visits: 13,669
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
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: Thursday, August 14, 2014 5:23 AM
Points: 287, Visits: 405
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
Posted Tuesday, September 25, 2012 3:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 2, 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
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: Thursday, August 14, 2014 5:23 AM
Points: 287, Visits: 405
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
Posted Tuesday, September 25, 2012 7:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1364044
Posted Tuesday, September 25, 2012 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1364046
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: Thursday, August 14, 2014 5:23 AM
Points: 287, Visits: 405
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 12»»

Permissions Expand / Collapse