CODEWITHSUNDEEP
sqlsql-serversql-updaterowsconcatenation
Phil
Phil

Reputation: 41

SQL Server: Concat Rows / or Columns and update result to Column

Following Situation:

I have a dynamically (by columns) builded table ... eg:

 Rowid UniqueID Name Birthdate Town ....
   1     null    Joe  Jan-93    Cologne
   2     null    Nick Okt-00    London

I am building this TempTable to create an uniqueID for all Data in my DataBase The TempTable was created by two loops which run through all my DataBase Table & Columns and copy all primary key Data to this TempTable.

My aim is to update the UniqueID Column from my TempTable with the concat values of data ... eg: 

    UniqueID
  JoeJan-93Cologne
  NickOkt-00London

Do you have an idea how to update UniqueID ?

What I m thinking about is: 

Loop 1 going through all Tables
 Select Table of Schema
Loop 2 going through all Columns of Table
 Select Column of Schema

 Copy Column to my Temp 

 -- here an update  like ... set UniqueID = select concat(UniqueID, @Column) 
 -- from @table where RowID = RowID
End loop 2 
end loop 1

Is this possible Or do I have to open a third loop which is running through all rows and concat values ?

Upvotes: 1

Views: 81

Answers (2)

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try this

Update <YourTableName>
set UniqueId = ISNULL(Name, '') +  ISNULL(Cast(Birthdate as Varchar(10), '') + ISNULL(Town, '')

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use CONCAT() with UPDATE statement, no any loop required :

UPDATE t
     SET UniqueID = CONCAT(Name, Birthdate, Town);

Upvotes: 0

Related Questions