sql server - MS SQL Store Procedure to Merge Multiple Rows into Single Row based on Variable Table and Column Names -

sql server - MS SQL Store Procedure to Merge Multiple Rows into Single Row based on Variable Table and Column Names -

i'm working ms sql server 2008. i'm trying create stored procedure merge (perhaps) several rows of info (answers) single row on target table(s). uses 'table_name' field , 'column_name' field answers table. info looks this:

answers table -------------- id int table_name varchar column_name varchar answer_value varchar

so, target table (insert/update) come 'table_name'. each row anwsers fill 1 column on target table.

table_name_1 table -------------- id int column_name_1 varchar column_name_2 varchar column_name_3 varchar etc...

note, there can many target tables (variable answers table: table_name_1, table_name_2, table_name_3, etc.) insert many columns (column_name_1...2...3) on each target table.

i thought using while statement loop through answers table. build variable insert/update statement(s) target tables. executing statements somehow. noticed merge looks might help problem (select/update/insert), ms sql stored procedure experience little. suggestion strategy or solution problem?

note 6/23/2014: i'm considering using single merge statement, i'm not sure possible.

i'm missing something, basic thought solve problem utilize meta-programming, dynamic pivot. in particular case there layer create solution more difficult: result need in different execution instead of beeing grouped.

the backbone possible solution is

declare @cols nvarchar(max) declare @query nvarchar(max) --using cursor on select distinct table_name answers iterate: --*cursor begin here* --mock variable first value of cursor declare @table nvarchar(max) = 't1' -- column list select @cols = stuff((select distinct ',' + quotename(column_name) answers (nolock) table_name = @table xml path(''), type ).value('.', 'nvarchar(max)') , 1, 1, '') --query definition set @query = ' select ' + @cols + ' ' + @table + ' (select column_name, answer_value answers table_name = ''' + @table + ''') b pivot (max(answer_value) column_name in (' + @cols + ' )) p ' --select @query exec sp_executesql @query --select verify execution --select * t1 --*cursor end here*

sqlfiddle demo

the cursor definition omitted, because i'm not sure if it'll work on sqlfiddle

in add-on template dynamic pivot columns list filtered new table name, , in query definition there select ... into instead of select.

this script not business relationship table in database, if that's possibility query can divided in two:

set @query = ' select top 0 ' + @cols + ' ' + @table + ' (select column_name, answer_value answers table_name = ''' + @table + ''') b pivot (max(answer_value) column_name in (' + @cols + ' )) p '

to create table without data, if needed, and

set @query = ' insert ' + @table + '(' + @cols + ')' select ' + @cols + ' (select column_name, answer_value answers table_name = ''' + @table + ''') b pivot (max(answer_value) column_name in (' + @cols + ' )) p '

or merge insert/update values in table. possibility drop , recreate every table.

sql sql-server sql-server-2008 stored-procedures