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

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  February 2, 2018 at 3:12 am

  #1978320

  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

  February 2, 2018 at 4:06 am

  #1978323

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