CSV Nesting and Parsing
-
Please excuse me if you find the following problem a bit irritating or weird but its just a complex query I have in mind. Kindly do not suggest normalising the table(s) as I wish to resolve it as is.
Ok, here's the situation, I've got two tables:
Table1.ID - Table1.PlaceHolderText
-----------------------------------
1 - Hello [Name], Is your age [Age]?
Table2.FKID - Table2.PlaceHolderName - Table2.PlaceHolderValues
---------------------------------------------------------------
1 - Name - John,Lucy,Jack
1 - Age - 16,24,30
Both tables can have multiple records (ie: unqiue Table1.ID's). Now, I wish to retrieve multiple records with nested parsing and string manipulation (basically text replace), such that the final resultset is:
1 - 1 - Hello John, Is your age 16?
2 - 1 - Hello John, Is your age 24?
3 - 1 - Hello John, Is your age 30?
4 - 1 - Hello Lucy, Is your age 16?
5 - 1 - Hello Lucy, Is your age 24?
6 - 1 - Hello Lucy, Is your age 30?
7 - 1 - Hello Jack, Is your age 16?
8 - 1 - Hello Jack, Is your age 24?
9 - 1 - Hello Jack, Is your age 30?
Any help or ideas will be of great help! Thank you.
Edited by - NickRice on 09/01/2003 05:25:56 AM
-
quote:
Kindly do not suggest normalising the table(s) as I wish to resolve it as is.any good reasons to make your life harder as it must be???
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Looks like you could use a cursor to break it up, then either loop through the rows to do the replace. First thought was a cross join if you trully want all the combos, but that doesnt help with the replace.
I appreciate that you're trying to solve the problem, but I agree with Frank - this is horrible, why wouldnt you fix, especially if its not working to start with? Even if you leave the existing tables as is, you can put a trigger on the table to normalize on insert (or do as a batch), then use that to perform your processing. Essentially, do the work once instead of every time.
Andy
-
Ok. I've normalised the table as follows:
Table1.ID - Table1.PlaceHolderText
------------------------------------
1 - Hello [Name], Is your age [Age]?
Table2.FKID - Table2.PlaceHolderName - Table2.PlaceHolderValue
--------------------------------------------------------------
1 - Name - John
1 - Name - Lucy
1 - Name - Jack
1 - Age - 16
1 - Age - 24
1 - Age - 30
How can I go about the parsing procedure now? It's driving me nuts and I have a gut feeling that there is a reliable solution to it.
-
This is still a hard problem to tackle. Especially the parsing of the string in Table1...
If you have a working solution for that, you can get all possible combinations using the following query
SELECT T2_Part1.Name, T2.Part2.Age
FROM (SELECT PlaceHolderValue FROM T2
WHERE PlaceHolderName = 'Name'
AND FKID = 1) T2.Part1
CROSS JOIN
(SELECT PlaceHolderValue FROM T2
WHERE PlaceHolderName = 'Age'
AND FKID=1) T2_Part2 -
Need a bit more work but may help a bit
Set Nocount On
GO
Create Table Tmp1(ID int,PlaceHolderText varchar(100))
GO
Insert Tmp1 values(1,'Hello [Name], is your age [Age] and do you live in the city of [Town]?')
GO
Create Table Tmp2 (FKID int,PlaceHolderName varchar(100),PlaceHolderValues varchar(100))
GO
Insert Tmp2 values(1,'Name','John')
Insert Tmp2 values(1,'Name','Lucy')
Insert Tmp2 values(1,'Name','Jack')
Insert Tmp2 values(1,'Age','16')
Insert Tmp2 values(1,'Age','24')
Insert Tmp2 values(1,'Age','30')
Insert Tmp2 values(1,'Town','Boston')
Insert Tmp2 values(1,'Town','New York')
Insert Tmp2 values(1,'Town','Auckland')
GO
Create Function ResolveFunnies(@Arg varchar(8000))
Returns @Answers Table (v Varchar(8000)) As
Begin
Declare @Tmp Table(Cnt Int,v varchar(8000))
Declare @v Varchar(1000),
@Cnt Int
Set @v='['
Set @Cnt=0
Insert @Tmp
Select 0,PlaceHolderText From Tmp1
Select @v='['+Min(PlaceHolderName)+']' From Tmp2
Where '['+PlaceHolderName+']'>@v
While @v Is Not Null
Begin
Insert @Tmp
Select @Cnt+1,Stuff(v,CharIndex(@v,v),DataLength(@v),PlaceHolderValues)
From @Tmp inner join Tmp2
On CharIndex(@v,v)>0 And '['+PlaceHolderName+']'=@v
Select @v='['+Min(PlaceHolderName)+']'
From Tmp2
Where '['+PlaceHolderName+']'>@v
Delete From @Tmp
Where Cnt=@Cnt
Set @Cnt=@Cnt+1
End
Insert @Answers
Select v From @Tmp
Return
End
go
Declare @v varchar(8000)
Select @v=PlaceHolderText
From Tmp1
Select * from ResolveFunnies(@v) Order by v
GO
Drop table Tmp1
Drop table Tmp2
Drop Function ResolveFunnies
GO -
Bug in function.
Create Function ResolveFunnies(@Arg varchar(8000))
Returns @Answers Table (v Varchar(8000)) As
Begin
Declare @Tmp Table(Cnt Int,v varchar(8000))
Declare @v Varchar(1000),
@Cnt Int
Set @v='['
Set @Cnt=0
Insert @Tmp
Select 0,PlaceHolderText From Tmp1
Select @v='['+Min(PlaceHolderName)+']' From Tmp2
Where '['+PlaceHolderName+']'>@v And
Exists(Select 1 From @Tmp Where CharIndex('['+PlaceHolderName+']',v)>0)
While @v Is Not Null
Begin
Insert @Tmp
Select @Cnt+1,Stuff(v,CharIndex(@v,v),DataLength(@v),PlaceHolderValues)
From @Tmp inner join Tmp2
On CharIndex(@v,v)>0 And '['+PlaceHolderName+']'=@v
Select @v='['+Min(PlaceHolderName)+']'
From Tmp2
Where '['+PlaceHolderName+']'>@v And
Exists(Select 1 From @Tmp
Where CharIndex('['+PlaceHolderName+']',v)>0 And @Cnt+1=Cnt)
Delete From @Tmp
Where Cnt=@Cnt
Set @Cnt=@Cnt+1
End
Insert @Answers
Select v From @Tmp
Return
End
go
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply