February 2, 2018 at 3:01 am
Hi,
Can any 1 help in using colasce in Update statement. I have the below query.
begin tran
Create table Vtest1
(
tran_no varchar(20),
files_name varchar(500)
)
Create table Vtest2
(
tran_no varchar(20),
file_path varchar(500)
)
insert into Vtest1 values('tran1','File1.PDF')
insert into Vtest1 values('tran1','File2.PDF')
insert into Vtest1 values('tran1','File3.PDF')
insert into Vtest1 values('tran2','File1.PDF')
insert into Vtest1 values('tran2','File2.PDF')
insert into Vtest1 values('tran3','File1.PDF')
insert into Vtest2(tran_no)
Select distinct tran_no
From Vtest1
Update t2
Set file_path = coalesce(file_path + ';' ,'') + files_name
From Vtest2 t2
Join Vtest1 t1
on t2.tran_no = t1.tran_no
Select *
From vtest2
Drop table Vtest1
Drop table Vtest2
rollback tran
the output required in Vtest2 table is
tran_no File_path
tran1 File1.PDF;File2.PDF;File3.PDF
tran2 File1.PDF;File2.PDF
tran3 File1.PDF
It is working if i use while loop like below
begin tran
Declare @serial_no int
Declare @sno int
Declare @count int
Declare @files varchar(4000)
Declare @tran_no varchar(20)
Create table Vtest1
(
tran_no varchar(20),
files_name varchar(500)
)
Create table Vtest2
(
tran_no varchar(20),
rownumber int,
file_path varchar(500)
)
insert into Vtest1 values('tran1','File1.PDF')
insert into Vtest1 values('tran1','File2.PDF')
insert into Vtest1 values('tran1','File3.PDF')
insert into Vtest1 values('tran2','File1.PDF')
insert into Vtest1 values('tran2','File2.PDF')
insert into Vtest1 values('tran3','File1.PDF')
insert into Vtest2(tran_no)
Select distinct tran_no
From Vtest1
Update vtest2
Set rownumber = @serial_no,
@serial_no = ISNULL(@serial_no,0) + 1
Select @sno = 1
Select @count = COUNT('x')
From Vtest2
While @sno <= @count
Begin
Select @tran_no = tran_no
From vtest2
Where rownumber = @sno
Select @files = coalesce(@files+';','') + files_name
From vtest1
Where tran_no = @tran_no
Update vtest2
Set file_path = @files
Where rownumber = @sno
Select @sno = @sno + 1
Select @files = NULL
End
Select *
From vtest2
Drop table Vtest1
Drop table Vtest2
rollback tran
Can any1 suggest since if transactions are more loop will take bit of time to complete
February 2, 2018 at 4:06 am
Thanks that worked!!!. It was for a report output and not storing data.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy