SQL rows to concatenated string

How to turn a list of (e.g.) countries into a concatenated string (e.g. France, New Zealand, California)

There’s a comprehensive post about different methods at https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/#Toc205129485 : the method I’ve used is “Scalar UDF with t-SQL update extension”

ALTER function [dbo].[GetCountriesOfOrigin](@productId int) returns 
 declare @result varchar(max) 
 set @result = '' 
 select @result = @result + c.CountryOfOrigin + ', ' from tblProductCountry pc 
 inner join tblCountryOfOrigin c on pc.CountryOfOriginID=c.CountryOfOriginID 
 where pc.ProductID=@productId 
 order by c.CountryOfOrigin
 set @result = ltrim(rtrim(@result))
 if right(@result,1)=',' set @result=left(@result, len(@result)-1) --damn LEN! ignores trailing spaces
 if len(@result)=0 set @result=null
 return @result

Enable data audit and tracking

This sql script will add system versioning to all your tables in your existing SQL 2016 database.
For example table [Stuff] will have a [StuffHistory] table where all old values are logged.
As we have existing data, we have to do each table in four SQL statements and I don’t think we can make the columns HIDDEN.

 Set all tables in this database as SYSTEM VERSIONED TEMPORAL TABLES
 Version 1.1
 Nick Ajderian
Enable data audit and tracking
References: https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table
Convert existing table to temporal table
*/ /* create table stuff6(stuffid int identity primary key, body nvarchar(200)) update stuff6 set body=body+'x' insert into stuff6(body) values('hello ') select * from stuff6 select * from stuff6history */ declare @sql1 nvarchar(1024),@sql3 nvarchar(1024),@sql4 nvarchar(1024), @sql2 nvarchar(1024) declare @sqlx nvarchar(1024) set @sql1='ALTER TABLE TABLE_NAME ADD SysStartTime datetime2(0) not null DEFAULT SYSUTCDATETIME() , SysEndTime datetime2(0) not null DEFAULT CONVERT(datetime2 (0), ''9999-12-31 23:59:59.99999999'') ' set @sql2='UPDATE TABLE_NAME SET SysStartTime = ''19000101 00:00:00.0000000'', SysEndTime = ''99991231 23:59:59.9999999'' ' set @sql3='ALTER TABLE TABLE_NAME ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);' set @sql4='ALTER TABLE TABLE_NAME SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TABLE_NAMEHistory))' --GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_SysStart --GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_SysEnd declare @tn nvarchar(200) declare c cursor for select name from sys.tables where not ( name like '%History' ) and not exists( select * from sys.tables tt where tt.name=sys.tables.name+'history' ) and name not in ('temp_NHSPatients','Diagnosis','EinsteinData','tbl_DiagnosisImport','tbl_OverwriteCCGs_KH','tbl_OverwriteCCGs_KH2') order by name open c fetch next from c into @tn while @@FETCH_STATUS=0 begin print 'Adding temporal fields to ' + @tn set @sqlx = replace(@sql1,'TABLE_NAME', @tn) exec (@sqlx) set @sqlx = replace(@sql2,'TABLE_NAME', @tn) exec (@sqlx) set @sqlx = replace(@sql3,'TABLE_NAME', @tn) exec (@sqlx) set @sqlx = replace(@sql4,'TABLE_NAME', @tn) exec (@sqlx) fetch next from c into @tn end close c deallocate c