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

Question

I'm working with MS SQL Server 2008. I'm trying to create a stored procedure to Merge (perhaps) several rows of data (answers) into a single row on target table(s). This uses a 'table_name' field and 'column_name' field from the answers table. The data looks like something like this:

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

So, the target table (insert/update) would come from the 'table_name'. Each row from the anwsers would fill one column on the target table.

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

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

I thought about using a WHILE statement to loop through the answers table. This could build a variable which would be the insert/update statement(s) for the target tables. Then executing those statements somehow. I also noticed Merge looks like it might help with this problem (select/update/insert), but my MS SQL Stored Procedure experience is very little. Could someone suggestion a strategy or solution to this problem?

Note 6/23/2014: I'm considering using a single Merge statement, but I'm not sure it is possible.

The expected result are a bunch of tables with one row of data? Or I'm missing something? — Serpiton
– Serpiton, Commented Jun 23, 2014 at 13:59
@Serpiton - Yes, in most cases there might be (20+) answers rows that would need to be inserted into 1 row in the table_name table. I'm trying to do this dynamically if possible. — jjwdesign
– jjwdesign, Commented Jun 23, 2014 at 14:04
It can be done, but if you need the values in that particular format you get it by PIVOTing them without creating the tables, is that part really necessary? — Serpiton
– Serpiton, Commented Jun 23, 2014 at 14:13
@Serpiton - BTW, I'm still learning MS SQL Stored Procedures. I'm more of a PHP developer. PIVOT'ing looks very promising. I'll give it a try. That should make the Merge more simple. — jjwdesign
– jjwdesign, Commented Jun 23, 2014 at 14:34
To find some examples of dynamic pivoting in SQLServer you can write (sql server dynamic pivot)[stackoverflow.com/search?q=sql+server+dynamic+pivot] in the search box, I can also write a template in an answer. The important part, IMO, is if you really need the result in different tables or a resultset like ID | table_name | answer_1 | ... | answer_n is enough — Serpiton
– Serpiton, Commented Jun 23, 2014 at 14:44

Serpiton · Accepted Answer · 2014-07-16 19:41:59Z

I'm probably missing something, but the basic idea to solve the problem is to use meta-programming, like a dynamic pivot.
In this particular case there is another layer to make the solution more difficult: the result need to be in different execution instead of beeing grouped.

The backbone for a possible solution is

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)

--using a cursor on SELECT DISTINCT table_name FROM answers iterate:
--*Cursor Begin Here*
  --mock variable for the first value of the cursor
  DECLARE @table AS NVARCHAR(MAX) = 't1'

  -- Column list
  SELECT @cols = STUFF((SELECT distinct 
                               ',' + QUOTENAME(column_name)
                        FROM answers with (nolock)
                        WHERE table_name = @table
                        FOR XML PATH(''), TYPE
                       ).value('.', 'NVARCHAR(MAX)') 
                      , 1, 1, '')

  --Query definition
  SET @query = '
  SELECT ' + @cols + ' 
  INTO   ' + @table + '
  FROM   (SELECT column_name, answer_value 
          FROM   answers 
          WHERE  table_name = ''' + @table + ''') b
         PIVOT (MAX(answer_value) FOR column_name IN (' + @cols + ' )) p '

  --select @query

  EXEC sp_executesql @query

  --select to verify the execution
  --SELECT * FROM t1
--*Cursor End Here*

SQLFiddle Demo

The cursor definition is omitted, because I'm not sure if it'll work on SQLFiddle

In addition to the template for a Dynamic Pivot the columns list is filtered by the new table name, and in the query definition there is a SELECT ... INTO instead of a SELECT.

This script does not account for table already in the database, if that's a possibility the query can be divided in two:

  SET @query = '
  SELECT TOP 0 ' + @cols + ' 
  INTO   ' + @table + '
  FROM   (SELECT column_name, answer_value 
          FROM   answers 
          WHERE  table_name = ''' + @table + ''') b
         PIVOT (MAX(answer_value) FOR column_name IN (' + @cols + ' )) p '

to create the table without data, if needed, and

  SET @query = '
  INSERT INTO   ' + @table + '(' + @cols + ')'
  SELECT ' + @cols + ' 
  FROM   (SELECT column_name, answer_value 
          FROM   answers 
          WHERE  table_name = ''' + @table + ''') b
         PIVOT (MAX(answer_value) FOR column_name IN (' + @cols + ' )) p '

or a MERGE to insert/update the values in the table.
Another possibility will be to DROP and recreate every table.

jjwdesign · Accepted Answer · 2014-07-16 13:26:13Z

0

Approach I took to this complex problem:

Create several temporary tables to work with your data
Select and populate the temporary tables with the data
Use dynamic pivoting to pivot the rows into one row
Use a CURSOR with WHILE loop for multiple table entries
SET @query with the dynamically built MERGE statement
EXECUTE(@query)
Drop temporary tables

answered Jul 16, 2014 at 13:26

jjwdesign

3,3229 gold badges43 silver badges67 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related