I have dynamically created a temp table #temp1 that create column dynamically based on a json string. The table will look like as below
#temp1
ID | Field | FRUIT | VEGE |
---|---|---|---|
1 | Field1 | A | B |
2 | Field2 | C | D |
I would like to fill in the value column only in result table based on the #temp1 table, the field and type_id is already filled beforehand
result table
ID | field | type_id | value |
---|---|---|---|
1 | Field1 | 1 | A |
2 | Field1 | 2 | B |
3 | Field2 | 1 | C |
4 | Field2 | 2 | D |
where the type_id can get from another table that specified the type and its id
type table
ID | type_id | type |
---|---|---|
1 | 1 | FRUIT |
2 | 2 | VEGE |
The type table will have many different types in it, I just list out the only two that is used in this question only.
Please help!
my attempt query:
x
update B
set B.value = ' value B.column_name in #result match with'
from result_table B
inner join type_table D
on D.type = 'column name in result_table'
Advertisement
Answer
You will need to use Dynamic Query
as your column is dynamic
declare @sql nvarchar(max),
@case_stmt nvarchar(max)
-- form the case statement
select @case_stmt = STRING_AGG ('WHEN ''' + type + ''' THEN t1.' + quotename(t.type), char(13))
from typ t
where exists
(
select *
from result r
where t.type_id = r.type_id
)
-- Print out for insepction
print @case_stmt
-- form final query
select @sql = 'update r' + char(13)
+ 'set value = CASE t.type' + char(13)
+ @case_stmt + char(13)
+ 'END'+ char(13)
+ 'from result r' + char(13)
+ 'inner join #temp1 t1 on r.Field = t1.Field' + char(13)
+ 'inner join typ t on r.type_id = t.type_id' + char(13)
-- Print out for insepction
print @sql
exec (@sql);
select *
from result
note : char(13)
is new line. It helps to do simple formatting of the query