Coalesce in SQL Server 2008

Question

Coalesce in SQL Server 2008

vikramchander90

Ten Centuries

Points: 1093
More actions
February 2, 2018 at 3:01 am

#333290

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Why do you want to store denormalised data in your database, especially when that data is a duplicate of data that is normalised? If you insist on doing this, the best way to concatenate is like this.

John

vikramchander90 Ten Centuries Points: 1093 More actions · Answer 2

Thanks that worked!!!. It was for a report output and not storing data.