Dynamically combine all columns in a table based on value

Transact-SQL
, , , ,
1

Hi All.

Can you tell me a way to concatinate all the columns in table dynamically and insert into a another table based on the Code and join operator. If there is a join operator then we should have that included in just a row . the resultant table looks like dbo.script

drop table Dbo.Test1
go
create table Dbo.Test1
(
Code varchar(3),
fieldname varchar(32), ,
operator varchar(32),
operand varchar(32),
JoinOperator varchar(32),
OrderOfPrecedence Varchar(32)
)

insert into Dbo.Test1

select 'ad','Name1','not in', '(alex,tom)','AND',1
union
select 'ad','state' ,'=', 'Texas','',2
union
select 'adc','Name1','not in', '(alex1,tom2)','',1

drop table dbo.Script

create table dbo.Script
(
Code varchar(3),
Script varchar(max))

insert into dbo.Script
select 'ad','Name1 not in (''alex'',''tom'') AND state = ''texas'''
union
select 'adc','Name1 in (''rix'',''sam'')'

2

it's kinda crappy as I've had to hardcode for the = operator. I'm not sure what others you have in there, but they would have to be taken into account as well

drop table if exists #Test1
go
create table #Test1
(
Code varchar(3),
fieldname varchar(32), 
operator varchar(32),
operand varchar(32),
JoinOperator varchar(32),
OrderOfPrecedence Varchar(32),
Script varchar(max)
)

insert into #Test1(Code, fieldname, operator, operand, JoinOperator, OrderOfPrecedence)

select 'ad','Name1','not in', '(alex,tom)','AND',1
union
select 'ad','state' ,'=', 'Texas','',2
union
select 'adc','Name1','not in', '(alex1,tom2)','',1


update #test1 set 
Script = FieldName + ' ' + operator +' ' +  
					case when CharIndex('=',operator) > 0 then '''' else '' end + 
						Replace(Replace(Replace(Operand, '(','('''),',',''','''), ')',''')') 
					+ case when CharIndex('=',operator) > 0 then ''' ' else ' ' end 
					 + JoinOperator

SELECT code, 
STUFF( 
        (       SELECT ' ' + Script  
                FROM #test1 t2  
                WHERE t1.code=t2.code  
                FOR XML PATH('') 
        ), 
        1, 
        1,'' 
      ) AS t 
FROM #test1 t1 
GROUP BY code
1 Like
3

Thanks Mike. Its working.