August 12, 2003 at 12:06 pm
I have a table a with these values.
NumberName
1ee,ed,ff,er,fsd
2 rr,kk
I want store these values as below into other table b .
NUmberName
1ee
1ed
1ff
1er
1fsd
2rr
2kk
How can i do this.
Thanks in advance.
August 12, 2003 at 1:14 pm
I have a table a with these values.
Number Name
1 ee,ed,ff,er,fsd
2 rr,kk
I want store these values as below into other table b .
Number Name
1 ee
1 ed
1 ff
1 er
1 fsd
2 rr
2 kk
How can i do this.
Thanks in advance.
August 12, 2003 at 3:18 pm
I think this is what you are looking for....
DECLARE @ID INT, @Len INT, @Begin INT, @End INT, @Last INT, @val INT
DECLARE CUR CURSOR FOR SELECT Number, LEN(Name) FROM TempTable
OPEN CUR
FETCH NEXT FROM CUR INTO @ID, @Len
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @val = @Len
SELECT @End = 1, @Begin = 0, @Last = 0
WHILE @Len <> 0
BEGIN
Select @Begin = CHARINDEX(',',Name, @End) from TempTable
WHERE Number = @ID
IF @Begin = 0
BEGIN
Select SUBSTRING(Name,@End,5), @ID from TempTable
WHERE Number = @ID
END
ELSE
BEGIN
Select SUBSTRING(Name,@End, @Begin - @End), @ID from TempTable
WHERE Number = @ID
END
IF @Begin = 0
BEGIN
SELECT @End = @val
END
ELSE
BEGIN
SELECT @End = @Begin + 1
END
SET @Len = @val - @End
END
FETCH NEXT FROM CUR INTO @ID, @Len
END
CLOSE CUR
DEALLOCATE CUR
Thanks
August 13, 2003 at 10:06 am
To do it without a cursor, try this:
Drop Table#table1
Create Table#table1(Number int, Name_ varchar(50))
Insert #table1 Values (1, 'ee,ed,ff,er,fsd')
Insert #table1 Values (2, 'rr,kk')
Drop Table#table2
Create Table#table2 (Number int, Name_ varchar(10))
Declare@RowCount int
Set@RowCount = (Select Count(*) From #table1)
While @RowCount > 0 Begin
Insert Into#table2
Selectt1.Number
, CaseWhenCharindex(',', t1.Name_) > 0
ThenReplace(Left(t1.Name_, (Charindex(',', t1.Name_))), ',', '')
Elset1.Name_
End
From#table1t1
Update#table1
SetName_ = CaseWhenCharindex(',', Name_) > 0
ThenRight(Name_, Len(Name_) - Charindex(',', Name_))
Else''
End
From#table1
Delete#table1
WhereLen(Name_) = 0
Select@RowCount = (Select Count(*) From #table1)
End
Select *
From #table2
August 13, 2003 at 12:31 pm
Yeah! very nice, kkeeffe...always appreciate someone using "While" loops instead of cursors. Below is your code modified to:
1) Remove @Rowcount variable and use "IF @@Rowcount = 0 Break" instead. It's cheaper.
2) Removed Delete statement and added Where clause "Where Len(Name_) <> 0". Also cheaper, as it's checking a condition instead of modifying a row.
3) added "If object_id('tempdb..#table1') is not null Drop Table #table1". Would also suggest changing these tables to "Declare @Table1 Table (Number int, Name_ varchar(50))" if your version allows.
These modifications may seem trivial, but since it's a loop they can mean significant performance gains. Especially when compared to a cursor.
If object_id('tempdb..#table1') is not null Drop Table #table1
Create Table #table1 (Number int, Name_ varchar(50))
Insert #table1 Values (1, 'ee,ed,ff,er,fsd')
Insert #table1 Values (2, 'rr,kk')
If object_id('tempdb..#table2') is not null Drop Table #table2
Create Table #table2 (Number int, Name_ varchar(10))
While 1 = 1 Begin
Insert Into #table2
Select t1.Number
,
Case
When Charindex(',', t1.Name_) > 0
Then Replace(Left(t1.Name_, (Charindex(',', t1.Name_))), ',', '')
Else t1.Name_
End
From #table1 t1
Where Len(Name_) <> 0
IF @@Rowcount = 0 Break
Update #table1
Set Name_ =
Case When Charindex(',', Name_) > 0
Then Right(Name_, Len(Name_) - Charindex(',', Name_))
Else ''
End
From #table1
Where Len(Name_) <> 0
End
Select *
From #table2
Signature is NULL
August 14, 2003 at 8:44 am
Thanks, Calvin - responses like yours are why I'm addicted to this site - I'm self-taught, and am constantly learning things that make me want to go back an rewrite all my stuff.
My delete statement seemed clumsy, and your "Where" clause / @@Rowcount solution is quite cool.
Regarding checking to see if the table exists before dropping it, normally I do, just got lazy here. FYI, your table variable suggestion won't run for me - I suspect its because I'm on V7. We're upgrading to 2K in late August, so I'll be using it then - thanks much.
Regards,
K2
August 14, 2003 at 1:02 pm
Yeah, this is a great forum. I like the daily emails listed active topics...good stuff.
Yeah, I was thinking about how to reverse this process. For example, say you had a list of column names that you wanted to put into a Select. Check this out...probably could be done more simply...
If object_id('tempdb..#table1') is not null Drop Table #table1
Create Table #table1 (TableID int identity, QueryID int, ColName varchar(50))
Insert #Table1 Values (1,'ee')
Insert #Table1 Values (2,'rr')
Insert #Table1 Values (1,'ed')
Insert #Table1 Values (2,'kk')
Insert #Table1 Values (1,'ff')
Insert #Table1 Values (1,'er')
Insert #Table1 Values (1,'fsd')
declare @TableID int,
@QueryID int,
@QueryString varchar(8000)
select @TableID = 0,
@QueryID = 0
While 1=1
Begin
select @QueryString = NULL,
@TableID = 0
Select top 1@QueryID = QueryID
From#Table1
Where QueryID > @QueryID
IF @@Rowcount = 0 break
While 1 = 1
Begin
Select top 1@TableID = TableID
From#Table1
WhereQueryID = @QueryID and TableID > @TableID
If @@Rowcount = 0 Break
Selecttop 1 @QueryString = isnull(@QueryString + ', ', '')--Puts commas after each value
+ ColName
From#Table1
WhereQueryID = @QueryID and TableID = @TableID
END
Select 'Select ' + @QueryString + ' From Table' + cast(@QueryID as varchar)
END
Signature is NULL
August 14, 2003 at 3:10 pm
Thanks all for the help.
August 15, 2003 at 8:53 am
Calvin - nested loops - makes me want to go write VB...
Regards
k2
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply