Dynamic Replace Lookup Values



 
 create table test (col varchar(1000))
insert into test values ('DD4DDDDDDDDDDDDDDDDDDXXXXXXXXXXXXXXXXXXXXDDDDDDDDDDDDDD4444DDD')
, ('4XXDD4DDDDDDDDDDDDDDDDDXXXXXXXXXXXXXXXXXXXXDDDDDDDDDDD4444DDD')
, ('4DX')


create table test1 (keys char(1),val varchar(20))
insert into test1 values ('D','SXM'),('X','DSN'),('4','MXD')


declare @ColumnHeaders VARCHAR(MAX)
, @ColumnHeaders1 VARCHAR(MAX)
, @ColumnHeaders2 VARCHAR(MAX)
;


Select  @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + quotename(keys,'[') 
                             FROM test1
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')

,@ColumnHeaders1 = STUFF( (SELECT DISTINCT ',' + quotename(keys,'[') +' as val' +  cast(row_number() Over(order by keys)  as varchar(3))
+',' + quotename(keys,'''') +' as key' +  cast(row_number() Over(order by keys)  as varchar(3))+','''+char(200+row_number() Over(order by keys))  +''' as sub'+ cast(row_number() Over(order by keys)  as varchar(3))
                             FROM test1
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
 ,@ColumnHeaders2= Replicate('Replace(', @@rowcount*2)   +'col,' + STUFF( (SELECT DISTINCT', ' +' key' +  cast(row_number() Over(order by keys)  as varchar(3))+',sub'+ cast(row_number() Over(order by keys)  as varchar(3)) +')'
                             FROM test1
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '') 
						 
							 
+','+ STUFF( (SELECT DISTINCT ',sub'+ cast(row_number() Over(order by keys)  as varchar(3)) +',val' +  cast(row_number() Over(order by keys)  as varchar(3)) +')'
                             FROM test1
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')  + ' as ColReplaced'; 
							 
	
 --print @ColumnHeaders2 
 

 
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 
    ';with mycte as (
	Select * from test, (
	SELECT ' + @ColumnHeaders1 +' FROM
     (select keys, val from test1) src
     PIVOT  (max(val) for keys in (' + @ColumnHeaders + ') ) pvt) t)
	 
	Select  '+ @ColumnHeaders2 + ' from mycte ';

--print @sql
EXEC(@SQL)


 
drop table test,test1



 

https://social.msdn.microsoft.com/Forums/en-US/ee264457-d02a-47ca-a847-93c9a9e7fa70/replace-a-char-with-a-string-dynamically?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s